Time intelligence

01The problem

Kolanut's board pack has a KPI card: "Revenue 2026: ₦290.7m, −46.1% vs last year." The board is alarmed. In fact revenue is up 19.1% on the same months of last year. The data runs only to 30 June 2026, so the card compared six months with twelve.

Time comparisons are what managers ask for most: this year so far, against last year, against last month, the last three months. DAX has a family of time intelligence functions that make them short to write, but they only give honest answers if the date table is right and you handle incomplete periods deliberately.

02The concept

Prerequisites

Time intelligence functions work on a proper date table: one row per day with no gaps, covering whole years, marked as a date table, and related to the fact table. You built exactly that in lesson 1.

The core functions

Each one returns a set of dates, which you use as a filter in CALCULATE:

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

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

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

MoM % = DIVIDE ( [Revenue] - [Revenue PM], [Revenue PM] )

Revenue Rolling 3M =
CALCULATE (
    [Revenue],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

TOTALYTD ( [Revenue], 'Date'[Date] ) is a shortcut for the YTD measure. For a financial year ending 30 June, DATESYTD ( 'Date'[Date], "30/6" ) restarts the count each 1 July.

The incomplete-year trap

At year level, the 2026 filter contains every date from 1 January to 31 December 2026, because the date table covers whole years. SAMEPERIODLASTYEAR shifts that to the whole of 2025, and you're comparing six months of sales with twelve.

The fix is to limit the current period to dates that have sales before shifting. Add a calculated column to the date table:

Date With Sales = 'Date'[Date] <= MAX ( orders[order_date] )

In a calculated column there's no filter on orders, so MAX ( orders[order_date] ) is the last sale overall: 30 June 2026. Then:

Revenue LY (like for like) =
CALCULATE (
    [Revenue],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        'Date'[Date With Sales] = TRUE ()
    )
)

CALCULATETABLE first trims the current dates to those up to 30 June 2026, and then SAMEPERIODLASTYEAR shifts them back a year. At year level, 2026 is now compared with January to June 2025.

03Example

A matrix with Date[Year] and Date[Month] in Rows:

With the plain Revenue LY, the 2026 row would show ₦539.8m and −46.1%. The monthly rows are identical with either measure. The trap only appears on rows that include dates with no sales yet.

04Walkthrough

1. Add Revenue YTD, Revenue LY, Revenue PM, MoM % and Revenue Rolling 3M. Format the percentages with one decimal place.
2. Build the matrix from the example with [Revenue], [Revenue LY] and a YoY % measure: DIVIDE ( [Revenue] - [Revenue LY], [Revenue LY] ). Find the −46.1% on the 2026 row.
3. Add the Date With Sales column and Revenue LY (like for like), and change YoY % to use it. The 2026 row becomes +19.1%.
4. Add Revenue YTD to the matrix and check that it restarts at January 2026.
5. Put Revenue Rolling 3M on a line chart by Date[Year Month]. It smooths out December 2025's festive peak, which is why managers like rolling figures for spotting trends.

05Practice

06Challenge

07More practice

08Check your understanding