Microsoft Office Tutorials and References
In Depth Information
If you look at the Figure 6-38, you can see a YTD column with the correct value already
calculated. First of all, to create the necessary DAX formula, you need to describe the required
algorithm. A simple (and not complete) definition might be sum all the values of the rows
with a date less than or equal to the date of the row considered for the calculation . However,
this is not enough because in this case the values for year 2010 would also include all the
values of rows belonging to year 2009. A better definition is sum all the values of the rows
with a year equal to the year of the row considered and with a date less than or equal to the
date of the row considered for the calculation.
FIguRE 6-38 A linked table in PowerPivot, with the calculated column YTD.
To get this result, you have to define the YTD formula in DAX. If you try to translate into DAX
the statement you just read in the English language, the problem to solve would be how to
define the FILTER condition because you have to compare the value of a column during the
FILTER operation with the value of the current row that is used to make the whole YTD
calculation. In other words, you might write something like this:
= SUMX( FILTER( Sales table, condition ),
where condition determines whether the row iterated in the FILTER condition is filtered or not,
comparing its date with the date of the current row in the external loop (which is the iteration
of the rows in the Sales table with which you calculate the value of YTD for each row).
It is important to remember what is going to happen: there will be a loop over all of the rows
of the Sales table. For each of these rows, the DAX expression is evaluated. The presence of a
FILTER function to get the subset of rows that have to be summed determines another loop,
and this means a new row context. At this point, every access to a column of this linked table
operates in the new row context, but this is a problem if you want to get the value of the
previous row context, the one that invoked the calculation of the YTD column.