Time Intelligence in DAX: YTD, MTD, Prior-Year, and Rolling Averages

Time intelligence is one of those PL-300 topics that looks like a vocabulary list — TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD — until you realize every one of these functions does the same underlying thing: it builds a modified date range and hands it to CALCULATE. Once you see that pattern, the specific function names stop mattering as much as knowing which range you actually need.

This post covers the four patterns that come up constantly, both on the exam and in real reports: year-to-date, month-to-date, prior-year comparison, and rolling averages. I'll also cover the one prerequisite that trips up more candidates than the functions themselves — your date table has to be built correctly, or none of this works reliably.

The prerequisite: a real, marked date table

Every time intelligence function in DAX depends on a contiguous date column from a table that Power BI recognizes as a date table. That means:

• A column of type Date (or Date/Time) with no gaps — every calendar day in your range, not just days that have transactions.
• The table marked as a date table via Mark as Date Table in Modeling, with that column set as the unique identifier.
• A relationship from your date table to the fact table's date column.

If you're using Auto Date/Time instead of a real date table, time intelligence functions will often still run without erroring — but they silently use Power BI's hidden auto-generated calendar hierarchy instead of your actual business calendar, which breaks the moment you have a fiscal year that doesn't start January 1, or you need a single unified calendar across multiple fact tables. I've covered building one properly in a separate post; assume from here on that you have one.

Year-to-date: TOTALYTD and DATESYTD

The most common pattern is a running total from the start of the year to the current filter context.

Total Sales = SUM(Sales[SalesAmount])

Sales YTD =
TOTALYTD([Total Sales], 'Date'[Date])

TOTALYTD is shorthand — under the hood it's equivalent to:

Sales YTD =
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date])
)

DATESYTD returns a table of dates from January 1 of the current year up to the last date in the current filter context, and CALCULATE evaluates [Total Sales] against that table instead of whatever date filter was active. If your fiscal year doesn't end December 31, both functions accept a year-end date argument — TOTALYTD([Total Sales], 'Date'[Date], "06-30") gives you a fiscal year ending June 30. This fiscal year-end parameter is a favourite thing for PL-300 case studies to test, because it's easy to forget and easy to get backwards.

Month-to-date follows the identical pattern with TOTALMTD / DATESMTD, and quarter-to-date with TOTALQTD / DATESQTD. Learn DATESYTD properly and the other two are the same function with a different grain.

Prior-year comparison: SAMEPERIODLASTYEAR vs DATEADD

This is where I see the most confusion, because two functions solve overlapping problems in slightly different ways.

Sales PY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Sales YoY % =
DIVIDE([Total Sales] - [Sales PY], [Sales PY])

SAMEPERIODLASTYEAR shifts the current date filter back exactly one year, preserving whatever range was already in context — a full year stays a full year, a single month stays that same month a year earlier. It's built specifically for year-over-year comparisons and nothing else.

DATEADD is the general-purpose version:

Sales Prior Period =
CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, MONTH)
)

DATEADD takes an interval argument — DAY, MONTH, QUARTER, or YEAR — so it handles month-over-month and quarter-over-quarter comparisons that SAMEPERIODLASTYEAR simply can't do. If a question only asks for a prior-year figure, SAMEPERIODLASTYEAR is the more readable choice; if you need any other interval, reach for DATEADD. Both require the same marked date table — neither works reliably against a raw transaction date column.

Rolling averages: AVERAGEX with DATESINPERIOD

Rolling averages (a trailing 3-month average, a trailing 12-month average) don't have a dedicated one-word function, and that's deliberate — it's a composition of AVERAGEX iterating over a custom date window built with DATESINPERIOD.

Sales 3-Month Rolling Avg =
AVERAGEX(
    DATESINPERIOD(
        'Date'[Date],
        LASTDATE('Date'[Date]),
        -3,
        MONTH
    ),
    [Total Sales]
)

DATESINPERIOD returns a table of dates spanning the given interval, counting backward from the reference date — here, three months back from the last date in the current context. AVERAGEX then iterates that date table one date at a time, evaluates [Total Sales] for each, and averages the results. This is a genuinely different mechanism from TOTALYTD and SAMEPERIODLASTYEAR: those return a table for CALCULATE to filter by; AVERAGEX is doing row-by-row iteration over dates and averaging measure results, which means it correctly handles months with different numbers of trading days instead of just dividing a sum by a fixed count.

A mistake I see constantly: writing DIVIDE(CALCULATE([Total Sales], DATESINPERIOD(...)), 3) to get a "3-month average." That divides a 3-month sum by the literal number 3, which silently produces the wrong number the moment a month has partial data — a new product, a short first month, a report refreshed mid-month. AVERAGEX with DATESINPERIOD averages actual monthly values, so it degrades gracefully instead of quietly lying to you.

Putting it together

The pattern underneath all four of these: a date-range function (DATESYTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD) produces a table of dates, and either CALCULATE filters by it directly or AVERAGEX iterates over it. Once that clicks, the question stops being "which function do I memorize" and becomes "what date range do I actually need" — which is exactly the skill PL-300's case-study questions are testing, and the same skill that makes you fast at building these measures in a real report.