5.3 Time Intelligence Measures

Quick Answer: Time intelligence DAX functions enable period-over-period analysis (YTD, YoY, QTD, moving averages). They all require a proper date table marked as such. Key functions: TOTALYTD for year-to-date, SAMEPERIODLASTYEAR for the prior year, DATEADD for flexible shifts, and PARALLELPERIOD for full parallel periods.

Prerequisites

Time intelligence requires a date table that is marked (Table Tools > Mark as Date Table), has a Date-typed column, has no gaps in the range (contiguous), has unique values (one row per date), and covers all dates in related facts. If any requirement fails, time intelligence returns wrong results or errors. The auto date/time hierarchy can also drive simple time intelligence, but a dedicated marked date table is the reliable, recommended approach.

Year-to-Date

Revenue YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
Revenue YTD = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date]))   // equivalent
Revenue Fiscal YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6/30")  // FY ending June 30
Revenue QTD = TOTALQTD(SUM(Sales[Amount]), 'Date'[Date])
Revenue MTD = TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

The optional "MM/DD" argument on TOTALYTD sets the fiscal year-end; without it, TOTALYTD assumes a December 31 calendar year.

Year-Over-Year

Revenue Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

YoY Change =
VAR Cur = SUM(Sales[Amount])
VAR Prior = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN Cur - Prior

YoY % Change =
VAR Cur = SUM(Sales[Amount])
VAR Prior = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(Cur - Prior, Prior)

DATEADD vs. PARALLELPERIOD

DATEADD(dates, number, interval) shifts the current selection by any count and unit; SAMEPERIODLASTYEAR is exactly DATEADD(dates, -1, YEAR).

Revenue Prev Quarter = CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, QUARTER))
Revenue Prev Week = CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -7, DAY))

Revenue Full Prev Month = CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, MONTH))

Running Totals and Moving Averages

Running Total =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Moving Avg 3M =
AVERAGEX(
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
    CALCULATE(SUM(Sales[Amount]))
)

Other Time Intelligence Functions

Reusable Comparison Pattern

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

On the Exam

The PL-300 tests date-table requirements, TOTALYTD/QTD/MTD, SAMEPERIODLASTYEAR for YoY, the DATEADD vs. PARALLELPERIOD distinction, the fiscal year-end argument, and building both absolute and percentage YoY change.

Why a Dedicated Date Table Beats Auto Date/Time

Time intelligence functions internally call helpers like DATESYTD and SAMEPERIODLASTYEAR that need a continuous, complete sequence of dates to shift across. A real fact table has gaps, no sales on closed days, so you cannot anchor time intelligence to the fact's own date column. A dedicated date dimension with one row per calendar day, marked as the date table, gives the engine the gap-free spine it needs and lets you add fiscal calendars, custom hierarchies, and flags (weekday, holiday) that Auto Date/Time cannot.

This is why the very first troubleshooting step for "my YTD measure returns blanks or wrong totals" is almost always to confirm a proper, marked date table.

Reading a Time Intelligence Measure

Most time intelligence measures share one shape: CALCULATE(expression, <date filter function>). The date function returns a table of dates that becomes the new filter context, and the expression evaluates over the fact rows that fall in those dates. SAMEPERIODLASTYEAR('Date'[Date]) returns last year's equivalent dates; DATESYTD('Date'[Date]) returns every date from the start of the year through the latest date in context; DATESINPERIOD returns a sliding window.

Once you see these functions as table-returning filters rather than magic, you can compose them, for example wrapping a YTD measure in SAMEPERIODLASTYEAR to get prior-year YTD.

Common Exam Traps

Watch for these recurring catches. TOTALYTD without a fiscal-year string assumes a December 31 year-end, so any fiscal-calendar scenario needs the "MM/DD" argument. DATEADD shifts the selected dates and can return a partial period, while PARALLELPERIOD always returns the whole period, choosing the wrong one is the most frequent time-intelligence error. And every time intelligence function silently misbehaves if the date column is not contiguous, so a question that mentions "missing dates" is almost always pointing you back to the date-table requirements.

Building Comparisons by Composition

The practical payoff of treating date functions as table filters is that you can stack them to build a full comparison suite from one base measure. Define Revenue = SUM(Sales[Amount]), then Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])), then Revenue YTD = TOTALYTD([Revenue], 'Date'[Date]), and finally Revenue PY YTD = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Date'[Date])). Growth measures are just DIVIDE([Revenue] - [Revenue PY], [Revenue PY]).

Notice how each measure reuses the one below it rather than re-deriving the aggregation, this composition keeps the logic readable and is the reason calculation groups (which apply such patterns generically) are so valuable once the pattern count grows. On the exam, recognizing that a prior-year-YTD measure is simply YTD wrapped in SAMEPERIODLASTYEAR is the kind of compositional insight the questions probe.