DAX Time Intelligence Function

Question

I would like to Calculate the MTD, YTD of Sales I would like to ask if these two queries give different result.

TotalMTD 1 := TOTALMTD(SUMX(Sales,Sales[Amount]),Sales[Date])

TotalMTD 2 := TOTALMTD(SUM(Sales[Amount]),Date[Date])

What is the different when using Date Table as Date Column as compare to Date Column from the object of analysis?

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-12-03T22:21:03

[TotalMTD 1] will perform a cumulative total over each context. In other words it calculates the SUMX() for the first context then will sum that total to the next context total and that total to the next context an so on until reach the Sales[Date] current evaluated context. Note SUMX(Sales,Sales[Amount]) takes the entire table Sales table to evaluate each context and perform the calculation until the current sales date.
[Total1MTD 2] contrary to [TotalMTD 1] measure, calculates the total per context, no cumulative total is performed there since SUM() is evaluated for each context.

For example, consider this simple dataset, in this example the context is Category:

Category   Amount    Month --> This can be any date of month 1
   A        100        1 
   A        100        1   
   B         50        1         
   B         50        1         
   C         50        1         
   C         50        1
   C         50        1

Your measures should produce:

Category    TotalMTD 1    TotalMTD 2
   A           200          200          
   B           300          100       TotalMTD1 ----> Sum A total to B total
   C           450          150       TotalMTD1 ----> Sum A + B to C total

Also be aware of the Mark a Date Table setting since that could make both measures result different. You can Mark a table as Date Table column which affects your filters and metadata used by client applications to represent your model. If you marked your Date table as the Date Table of your model it will apply an ALL(Date) in each measure where you use Date[Date] column, i.e. your measure will be implicitely converted to:

TotalMTD 1 := TOTALMTD(SUMX(Sales,Sales[Amount]),Sales[Date],ALL(Date))

Which obviously performs a different calculation.

Additional information:

Time Intelligence functions are supposed to be used with the date column from a calendar/date table. That column should be a consecutive and secuencial key.

In order to work with time-intelligence functions, you need to have a date table included in your Data Model. The date table must include a column with one row for every day of each year included in your data.

REFERENCE 1
REFERENCE 2

Let me know if this helps.

DAX Time Intelligence Function

1 Answers