3.2.3. Time Intelligence Measures
💡 First Principle: Comparing time periods means asking the same question over a shifted or expanded set of dates: "sales, but for the same dates last year," "sales, but from January 1 up to this date." Time intelligence functions build those date sets from the marked date table, and CALCULATE applies them as filters.
All of these functions require a proper, marked, contiguous date table (3.1.4) and operate on its date column.
| Function | Returns | Typical measure |
|---|---|---|
TOTALYTD(expr, dates) / TOTALQTD / TOTALMTD | Expression accumulated from period start | TOTALYTD([Total Sales], 'Date'[Date]) |
DATESYTD(dates [, year_end]) | Table of dates from year start to current max date | CALCULATE([Total Sales], DATESYTD('Date'[Date], "6/30")) for a June fiscal year-end |
SAMEPERIODLASTYEAR(dates) | Current dates shifted back one year | Prior-year sales for the same period |
DATEADD(dates, n, interval) | Dates shifted by n days, months, quarters, or years | DATEADD('Date'[Date], -1, MONTH) for prior month |
PARALLELPERIOD(dates, n, interval) | The entire shifted period | Full prior quarter, regardless of selection within it |
DATESINPERIOD(dates, start, n, interval) | Dates in a window from a start date | Rolling 12 months |
DATESBETWEEN(dates, start, end) | Dates between two dates | Custom ranges |
PREVIOUSMONTH / NEXTMONTH / PREVIOUSYEAR etc. | Whole adjacent period | Prior full month |
Building a year-over-year comparison:
Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales Rolling 12M =
CALCULATE (
[Total Sales],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
)
Fiscal years are handled by the optional year-end argument of the YTD functions ("6/30") plus fiscal columns in the date table for grouping. Quick measures can generate year-over-year change and running totals if you'd rather start from a template.
Know the subtle difference between SAMEPERIODLASTYEAR/DATEADD (shift exactly the dates in context: if 1–15 March is selected, they return 1–15 March last year) and PARALLELPERIOD (returns the whole shifted month, quarter, or year: all of last March).
⚠️ Exam Trap: Time intelligence filters the date table, so the date column you pass must come from the marked date table, not from the fact table (Sales[OrderDate]). Using the fact column causes incorrect or blank results whenever a date has no sales, and the exam's "fix" is to reference 'Date'[Date].
Reflection Question: A user selects May 1–20, 2026. What dates does SAMEPERIODLASTYEAR return, what does PARALLELPERIOD('Date'[Date], -12, MONTH) return, and which should a "same-to-date" comparison use?
