Managers think in periods: this month vs last month, year to date, same quarter last year. DAX has time intelligence functions for exactly these comparisons, as long as your model has a proper date table.
Prerequisite: a date table
- One row per date, continuous, covering all your data.
- Marked as the date table.
- Related to the date column in your fact table.
Without it, most functions below return blanks or wrong numbers. See star schema and the date table.
The base measure
Revenue = SUM ( Sales[Revenue] )Every time intelligence measure builds on a simple base measure like this.
Year-to-date, quarter-to-date, month-to-date
Revenue YTD = TOTALYTD ( [Revenue], 'Date'[Date] )
Revenue QTD = TOTALQTD ( [Revenue], 'Date'[Date] )
Revenue MTD = TOTALMTD ( [Revenue], 'Date'[Date] )For a fiscal year starting in April, add the year-end date:
Revenue FYTD = TOTALYTD ( [Revenue], 'Date'[Date], "03-31" )Same period last year
Revenue LY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Revenue YoY % = DIVIDE ( [Revenue] - [Revenue LY], [Revenue LY] )Previous month and month-on-month
Revenue PM = CALCULATE ( [Revenue], DATEADD ( 'Date'[Date], -1, MONTH ) )
Revenue MoM % = DIVIDE ( [Revenue] - [Revenue PM], [Revenue PM] )DATEADD shifts the current date filter by any number of days, months, quarters or years.
Rolling three-month total and average
Revenue Rolling 3M =
CALCULATE (
[Revenue],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)
Revenue Rolling 3M Avg = DIVIDE ( [Revenue Rolling 3M], 3 )Year-to-date last year
Revenue YTD LY = CALCULATE ( [Revenue YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Useful for "how are we doing this year so far versus the same point last year?".
Handling incomplete periods
If data only runs to the 15th of the current month, comparing this month with all of last month is unfair. Common fixes:
- Compare to the same number of days last month.
- Hide future dates: add a column like
IsPastOrTodayto the date table and filter visuals on it. - Show the current month separately as "month to date".
Common mistakes
- No date table, or not marked as one.
- Using the fact table's date column in visuals instead of the date table's.
- Gaps in the date table.
- Forgetting the fiscal year-end argument.
- Showing percentages without the underlying numbers, which hides tiny bases.
Practise
Time intelligence is built on CALCULATE, so read CALCULATE explained first. The motor claims dashboard project asks for month-on-month comparisons that use these patterns.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: