Microsoft Office Tutorials and References
In Depth Information
What Date Column to use
Keep in mind that the date column that you must use when calling TOTALYTD (and
other similar functions) is the date column of the Dates table, and not the date column
of the table that is the object of analysis. In this case, the OrderDate[Date] column was
used instead of the SalesOrderHeader[OrderDate] column. If we had used the latter, the
calculation would have been wrong. You can see in Figure 7-38 the result that would
have been produced by using the following formula for the YTD measure:
YtdLineTotal = TOTALYTD( SUM( SalesOrderDetail[LineTotal] ),
FIguRE 7-38 The wrong year-to-date calculation returns the same value as the LineTotal measure.
The problem is that the existing filter on year and month would be still applied. There
are possible workarounds that you might use, but our suggestion is to always define
and use a Dates table. Further details on the issue and possible workarounds are
available in this blog post written by Kasper de Jonge: http://tinyurl.com/DaxTimeAll.