Javier Guillen posted on February 24, 2011 22:06
One of the interesting DAX Time Intelligence functions is the TOTALYTD, which can define a measure calculation over a year in a cumulative way.
The same output can be achieved in alternate ways using the CALCULATE function. Nevertheless, as it is a very common computation in Excel reports, the PowerPivot team has created a dedicated function in order to help developing YTD calculations much faster.
A TOTALYTD behavior that is worth nothing however, is the fact that if a filter context defines multiple years (perhaps accidentally), this function will then use the last year available in order to perform the aggregation.
The screenshots below show a simple pivot table using Adventure Works data. The DAX measure defined for YTD aggregate is:
TOTALYTD(SUM([SalesAmount]), DimTime[FullDateAlternateKey], ALL(DimTime))
When the slicer selection specifies only one year, the regular aggregate of [Sales Amount] over each month as well as the DAX [YTD] measure act on that sole year.
However when two years are selected, the regular aggregate of [Sales Amount] acts over the correct filter context (defined over two years), whereas the YTD measure uses only the last year (2002).
At first it can be confusing, but if you select different sets of multiple years in the slicers it becomes evident pretty quick that the YTD is only showing the latest year of the set.
Although it can be argued that it is a good assumption given that the TOTALYTD function is not meant to work over multiple years, it is still worth documenting this behavior and keeping it in mind in case a complex report produces unexpected results due to a YTD formula that is picking for you what year should go into the calculation.