Time intelligence

Year-to-date, prior period, and rolling windows — with the prerequisites that make them work.

Prerequisites

Time intelligence needs three things, and it fails quietly without them.

A dedicated Date table, contiguous with no missing days, covering the full range of your fact data including complete final and first years. And it must be flagged with Mark as Date Table.

Always filter and slice on the Date table's columns, not the fact table's date column. Filtering the fact directly bypasses the relationship logic these functions rely on.

Year to date and prior period

Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
Sales PY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = DIVIDE([Sales] - [Sales PY], [Sales PY])

DATEADD('Date'[Date], -1, YEAR) is the more general form and lets you shift by any interval. PARALLELPERIOD shifts and expands to the full period, which is different — worth knowing when a comparison looks oddly large.

For a non-calendar fiscal year, pass a year-end date: TOTALYTD(..., "30/06").

Rolling windows

Rolling 12M =
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

DATESINPERIOD takes an anchor date and counts backwards. DATESBETWEEN is the explicit alternative when you want fixed endpoints.

Use MAX('Date'[Date]) rather than TODAY() as the anchor so the measure works correctly inside a matrix showing historical rows.

The half-empty-year problem

A very common bug: your Date table stops at today, so TOTALYTD and prior-year comparisons behave oddly at the boundary.

Build the Date table to cover complete years — through 31 December of the final year — and add a flag column like IsPastDate = 'Date'[Date] <= TODAY() to filter visuals where you don't want future empty periods showing.