Time intelligence: month to date and year over year

#Why it matters

Every monthly business review asks the same things: how are we doing this month so far, compared with last month, and compared with the same month last year? DAX time intelligence functions answer these in one line each, but only on a proper date table. Getting them right is a standard PL-300 and interview topic.

#The concept

Time intelligence functions shift or extend the dates in the current filter context:

• DATESMTD, DATESQTD, DATESYTD: from the start of the period to the last date in context

• DATEADD ( Date[Date], -1, MONTH ): the same dates shifted back one month

• SAMEPERIODLASTYEAR ( Date[Date] ): the same dates one year earlier

They need a date table that is marked as a date table with one row per day and no gaps, related to the fact table.

#Worked example: monthly review measures

Revenue MTD =
CALCULATE ( [Revenue], DATESMTD ( 'Date'[Date] ) )

Revenue YTD =
CALCULATE ( [Revenue], DATESYTD ( 'Date'[Date] ) )

Revenue Prev Month =
CALCULATE ( [Revenue], DATEADD ( 'Date'[Date], -1, MONTH ) )

MoM Growth % =
DIVIDE ( [Revenue] - [Revenue Prev Month], [Revenue Prev Month] )

Revenue LY =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

On the Online Retail data by month:

December looks like a collapse, but the data stops on 9 December 2011. A fair comparison uses the same number of days. One way:

Last Sales Date = CALCULATE ( MAX ( Sales[InvoiceDay] ), ALL ( Sales ) )

Revenue Prev Month Same Days =
VAR LastDay = [Last Sales Date]
VAR DaysIn = DAY ( LastDay )
RETURN
    CALCULATE (
        [Revenue],
        DATEADD ( 'Date'[Date], -1, MONTH ),
        DAY ( 'Date'[Date] ) <= DaysIn
    )

Revenue LY is blank for most months, because the dataset covers only December 2010 to December 2011. Say so on the page instead of showing an empty chart.

#Common mistakes

• Using the fact table's date column in time functions. DATESYTD ( Sales[InvoiceDate] ) gives wrong results because the column has gaps and times. Always use the date table.

• Not marking the date table. Some functions still work, others silently misbehave with relationships on datetime columns.

• Comparing a partial period with a full one. The December example above is the most common wrong chart in monthly reviews.

#Interview angle

"Show me YTD and year-over-year growth" is a standard live task. Build it, then point out a partial-period risk unprompted. That shows you think about how the number will be read, not just how it is computed.

#Practice task

1. Add Revenue QTD and a Revenue Rolling 3M measure using DATESINPERIOD.

2. Build a line chart of Revenue and Revenue Prev Month Same Days.

3. Write a tooltip that explains the December caveat.

#Further reading

• Microsoft: time intelligence functions (DAX)

• Microsoft: set and use date tables in Power BI