Microsoft Office Tutorials and References
In Depth Information
Year-to-Date, Quarter-to-Date, and Month-to-Date
The calculation of year-to-date (YTD), quarter-to-date, (QTD) and month-to-date (MTD) are
all very similar. Obviously, month-to-date is meaningful only when you are looking at data at
the day level, whereas year-to-date and quarter-to-date calculations are often used to look
at data at the month level.
For example, in Figure 7-36, you can see the LineTotal measure aggregated by year, quarter,
FIguRE 7-36 The LineTotal measure aggregated by the corresponding period in a row.
You can calculate the year-to-date value of LineTotal for each month and quarter by using a
measure that operates on the filter context, modifying the filter context on dates for a range
that starts on January 1 and ends on the month corresponding to the calculated cell. You can
define a YtdLineTotal measure by using the following DAX formula:
YtdLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),
DATESYTD( OrderDate[Date] ) )
The CALCULATE function receives in its second parameter a table that contains the dates of the
year-to-date period that has to be considered in the aggregation. This set of dates is returned by
the built-in DATESYTD function, which is a Time Intelligence function that returns a list of all the
dates from the beginning of the year until the last date included in the current filter context.