Microsoft Office Tutorials and References
In Depth Information
You can see the new measure in action in Figure 7-37.
FIguRE 7-37 The LineTotal year-to-date measure side-by-side with a regular measure.
This approach requires that you deal with the CALCULATE function, but because this pattern
(using a CALCULATE and a DATESYTD function) is very common, PowerPivot offers a dedicated
DAX function that simplifies (and makes more readable) the syntax of the YTD calculation,
YtdLineTotal = TOTALYTD( SUM( SalesOrderDetail[LineTotal] ),
As you can see, the syntax requires the hoped-for aggregation as the first parameter and
then just the date column as the second parameter. The behavior is identical to the original
measure, but the name of the TOTALYTD function immediately communicates the intention
of the formula. However, you need to know the behavior of the original CALCULATE syntax
because it allows a more complex calculation that you define later in this chapter.