DAX
Advanced 14 min read DAX

Time Intelligence in DAX

Make every measure answer 'now vs then'

YTD, MTD, period-over-period, rolling windows: DAX time intelligence shifts the date context so a single measure answers 'now vs then' correctly.

time intelligenceYTDMTDSAMEPERIODLASTYEARDATESdate table
Prerequisites: Calculate and Filter Context
Before we start

What you will learn

  • Apply YTD/MTD and prior-period functions
  • Explain why a proper date table is mandatory
  • Build rolling and period-over-period measures
Introduction

What is this?

Time intelligence functions (TOTALYTD, TOTALMTD, DATESYTD, SAMEPERIODLASTYEAR, PREVIOUSMONTH, DATESINPERIOD) rewrite the date filter so one base measure (like [Revenue]) becomes a family: current month, prior month, YTD, rolling 90 days. They all assume a standalone date/dim-date table marked as the model's date table — no gaps, no duplicates — otherwise every period function returns garbage.

Motivation

Why do you need it?

  • Executives compare: this month vs last, YTD vs target — same base number.
  • Well-formed date tables make CALCULATE-based period math trivial.
  • It kills the 'hard-coded date in every measure' anti-pattern.
  • Interview correctness: YTD and year-over-year patterns are standard.
Think about it

Mental model

Imagine

A family calendar on the wall

Think of

The date table

The calendar is fixed — every day is on it, every month exactly once. DAX time functions clip the current slice (YTD = through today) and shift windows (last year = same span a year earlier) like turning the calendar's pages from the same day one year back. No missing days, no twin months — the calendar is trusted.

Mechanics

How it works

  • The date table covers the whole analysis span, marked 'Date' in the model.
  • TOTALYTD(expr, datecol, filter?) sums expr from period start to the context end.
  • SAMEPERIODLASTYEAR shifts the date column by exactly one year.
  • DATESBETWEEN/DATESINPERIOD build raw custom windows.
  • A 'relative period' measure composes TOTALYTD with CALCULATE + date shifts.

Time-intelligence context shifts on the calendar.

Selected period
TOTALYTD
Prev period
Base measure
Output
Hands-on

Examples

The standard time family

The standard time family
Revenue       = SUM(fact_orders[amount])
 
Revenue_YTD   = TOTALYTD([Revenue], dim_date[date])
Revenue_MTD   = TOTALMTD([Revenue], dim_date[date])
PLS           = SAMEPERIODLASTYEAR(dim_date[date])
 
Revenue_PY    = CALCULATE([Revenue], PLS)
Revenue_YoY   = DIVIDE([Revenue] - [Revenue_PY], [Revenue_PY])
 
Revenue_90d   = CALCULATE([Revenue],
                  DATESINPERIOD(dim_date[date], LASTDATE(dim_date[date]), 90, DAY))

One base measure plus window-shipping CALCULATE calls produces YTD, prior-year and rolling-90-days — all consistent with the same revenue definition.

Real world

In production

  • A monthly ops deck shows MTD, LYMTD and the delta from one base revenue measure.
  • YoY growth tiles use SAMEPERIODLASTYEAR composited on the current visibility.
  • A rolling 30/60/90 retention curve relies on DATESINPERIOD over a join date dim.
Guidelines

Best practices

  • Always mark a dense date table; never 'date' from the fact's own column.
  • Keep the base measure filterless and lean; push windows into computation.
  • Compose time windows in a measure, not column hard-coded boundaries.
  • Verify YTD against a month-end control total before publishing.
Watch out

Common mistakes

  • Time-intel functions with an un-contiguous date column → wrong windows.
  • Forgetting marking the date table → functions silently fail.
  • Slicing by 'month number' instead of the true date → YTD mixes years.
  • Hard-coding 'since Jan 1' strings into measures.
When things break

Debugging tips

  • YTD number wrong → check the date table's last date matches the calendar.
  • Prior period blank → verify the date column is continuous for the shift.
  • Rolling window drifting → confirm LASTDATE and the base filter recalc together.
Interviews

Interview checkpoint

Practice

Hands-on lab

Replicate YTD and Prior-Year in SQL
40 min · advanced
In progress
Objectives
  • Build YTD with a date spine equivalent
  • Reproduce prior-year + YoY in SQL
  • Prove the need for a contiguous date

Replicate YTD and Prior-Year in SQL

40 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which function shifts the date context exactly one year back?

2

Time intelligence works fine using the fact table's own date column.

Go deeper

Official documentation

Time intelligence functions
docs