Power BI
Intermediate 14 min read Power BI

Time Intelligence in DAX

Because 'compared to last year' is a filter-problem, not an arithmetic one

Time intelligence compares periods: same period last year, YTD, moving totals and shifts. All of it requires a proper date table and CALCULATE-driven measures.

time intelligencedate tableYTDSAMEPERIODLASTYEARTOTALYTDDATESINPERIODCALENDARAUTO
Prerequisites: Data Modeling in Power BI
Before we start

What you will learn

  • Explain why DAX time intelligence needs a contiguous date table
  • Write YoY, YTD and trailing-30-day measures
  • Handle missing dates and fiscal calendars in comparisons
Introduction

What is this?

Time intelligence is a family of DAX functions that shift, range and accumulate dates so measures can compare periods. They work by returning a set of dates that CALCULATE then applies: SAMEPERIODLASTYEAR returns last year's dates, TOTALYTD aggregates from year start, and DATESINPERIOD walks a sliding window. A dedicated date table with every calendar day is the prerequisite — the functions break when dates are missing.

Motivation

Why do you need it?

  • YoY growth is the default business question; hand-rolling it with SUM and filters is error-prone.
  • A correct date table also powers fiscal-year logic, holiday calendars and reliable empty-month handling.
  • Managers ask 'are we up or down', and time intelligence is how the answer stays correct per slicer.
  • Every interview includes a time-intelligence scenario; it is the most reused DAX pattern.
Think about it

Mental model

Imagine

Turning a pages of an annual ledger

Think of

Time intelligence in DAX

SAMEPERIODLASTYEAR is going back one full page; TOTALYTD is summing from the first page to the current page; DATESINPERIOD is marking a sticky-note window of N pages you want to compare.

Mechanics

How it works

  • A date table holds every date of the period with year, quarter, month flags.
  • A relationship joins the date table to the fact's date key.
  • SAMEPERIODLASTYEAR(Calendar[Date]) yields the shifted range the user's filter currently covers.
  • CALCULATE(SUM(sales), shifted range) recomputes the measure under the compared window.
  • TOTALYTD and TOTALQTD use the year boundary from the date table, respecting fiscal calendars.

Two windows returned by DAX time functions.

Filtered window
SAMEPERIODLASTYEAR
TOTALYTD
DATESINPERIOD -30d
Measures compare
Hands-on

Examples

Three canonical time measures

Three canonical time measures
Revenue = SUM(Orders[Amount])
 
Revenue LY =
  CALCULATE( [Revenue], SAMEPERIODLASTYEAR('Date'[Date]) )
 
Revenue YTD =
  TOTALYTD( [Revenue], 'Date'[Date] )
 
Revenue Trailing 30d =
  CALCULATE(
    [Revenue],
    DATESINPERIOD( 'Date'[Date], MAX('Date'[Date]), -30, DAY )
  )

Revenue is the base sum. SAMEPERIODLASTYEAR shifts the current window back one year, TOTALYTD accumulates from the year start, and DATESINPERIOD opens a 30-day rolling window from the latest visible date — each re-evaluated in whatever context the user sliced.

Real world

In production

  • A sales team ships a YoY measure that stays correct when someone filters to one month, thanks to SAMEPERIODLASTYEAR inside CALCULATE.
  • A finance model uses a fiscal calendar marking April as month 1; TOTALYTD respects the fiscal year end.
  • An analytics team builds a trailing-30-day revenue measure with DATESINPERIOD for an always-fresh KPI card.
Guidelines

Best practices

  • Always create a proper date table with CALENDARAUTO and a year column; never reuse a sparse date column.
  • Mark the table as a date table so DAX recognizes its calendar semantics.
  • Write comparative measures as CALCULATE(expr, window) instead of duplicating base logic.
  • Store fiscal flags (FiscalYear, PM) in the date table, not in DAX hard-codes.
Watch out

Common mistakes

  • Using date columns with holes (no weekends, no holidays) so shifts skip days.
  • Summing and then dividing the raw sum for a ratio instead of dividing inside the same context.
  • Forgetting the year boundary in YTD, causing a carried-over tail of the previous year.
  • Hard-coding 365 instead of using DATESINPERIOD for accurate windows across leap years.
Root cause analysis

Common issues

Error
YoY returns blank for the first month of data
Root cause

SAMEPERIODLASTYEAR has nothing to shift to — no 12-month-old rows

Fix

Extend the date table or use TOTALYTD with a fallback and show blank intentionally.

Error
YTD includes last December's tail
Root cause

The fact key and date table year boundary disagree on fiscal start

Fix

Align the fiscal year start in the date table with the finance calendar.

Error
Rolling 30 moves too slowly
Root cause

DATESINPERIOD anchored to the max date instead of the current row filter

Fix

Bound the window with the selected date context, e.g. using LASTDATE within the visual range.

When things break

Debugging tips

  • Temporarily render day-of-year and the raw date columns in a table visual to see which dates a window covers.
  • Isolate the base sum measure from the window-shifted one to compare against a known month.
  • Turn on the DAX query view to inspect the date ranges the formulas request.
  • Create a test date table with 60 rows to shrink debugging surface.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a YTD vs LY Dashboard
30 min · intermediate
In progress
Objectives
  • Create a marked date table
  • Wire Revenue, Revenue LY and Revenue YTD measures
  • Prove the compare stays correct under a month slicer
  • Handle the first-period blank

Build a YTD vs LY Dashboard

30 min · intermediate

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

Quick quiz

0/2
1

Which function shifts the current period one year back for comparison?

2

Time-intelligence functions work fine on a sparse date column with missing weekends.

Go deeper

Official documentation

Time intelligence functions (DAX)
docs
Automatically create a date table
docs