Time intelligence in DAX: YTD, like-for-like and rolling averages, done properly

Time intelligence in DAX means one base measure, such as Total Sales, wrapped in CALCULATE with a function that changes the dates it looks at: TOTALYTD for year to date, SAMEPERIODLASTYEAR for last year, DATESINPERIOD for a rolling window. All of them need a proper, marked date table. Most wrong numbers come from skipping it.

This guide builds the measures most UK finance and operations reports ask for, in the order you would build them, and flags where each one quietly goes wrong. If DAX itself is new, start with our plain-English DAX explainer; if you have written a few measures and some totals look odd, ten common DAX mistakes is the companion piece. Function behaviour below is checked against Microsoft Learn as of September 2026.

What does time intelligence need before you write a measure?

A date table: one row per day, no gaps, related to your fact table, and marked as the date table. Microsoft's date table documentation lists what marking validates: the date column must contain unique values, no nulls and contiguous dates from beginning to end. It also says you must mark the table if you use the classic time intelligence functions.

Marking matters for a second reason. When a marked date table's date column is filtered inside CALCULATE, Power BI clears the other filters on the date table, so a filter on Year Month does not fight the shifted dates. Leave the table unmarked and a rolling average can silently collapse to a single month.

A minimal DAX date table for a business with an April–March financial year:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Month", FORMAT ( [Date], "MMM YYYY" ),
    "Fiscal Year",
        "FY" & IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) )
)

Start and end on whole financial years, sort Month by Year Month, relate 'Date'[Date] to your fact table's date column, then Mark as date table. If you already shape your data in Power Query, build the table there instead; the rules are the same. Our star schema guide explains why Auto date/time is not a substitute.

Every example below uses one base measure:

Total Sales = SUM ( Sales[Amount] )

How do you calculate year to date in DAX?

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

That runs 1 January to the latest date in the current filter. Most UK businesses do not report on a calendar year, so pass a year end. Microsoft's TOTALYTD reference says the default is 31 December, the year part is ignored, and it recommends the month/day format:

Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )

Month to date and quarter to date work the same way with TOTALMTD and TOTALQTD.

Why does the YTD line keep going after the data stops?

Because the date table runs to March 2027 and the sales stop at yesterday. For October onwards, "year to date" is just the total so far, so the line goes flat across the rest of the year and anyone reading it assumes the business stalled. Stop the measure where the data stops:

Last Sales Date = CALCULATE ( MAX ( Sales[OrderDate] ), REMOVEFILTERS () )

Sales FYTD =
IF (
    MIN ( 'Date'[Date] ) <= [Last Sales Date],
    TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )
)

How do you compare with last year like-for-like?

The basic prior-year measure:

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

Microsoft's SAMEPERIODLASTYEAR reference says it returns the same dates as DATEADD ( dates, -1, YEAR ). Use DATEADD when you need other shifts, such as -1, MONTH for previous month.

Why is this month always down on last year?

Because it is only part of a month. Part-way through September, this year's September holds the days so far and last year's holds all 30. Every month-to-date comparison looks like a decline until the month closes, and a board pack full of red arrows invites the wrong conversation.

Like-for-like (LFL) fixes it by comparing only the days that have happened. Add a calculated column to the date table, which is worked out at each refresh:

Is Past = 'Date'[Date] <= MAX ( Sales[OrderDate] )

Then filter to those dates before shifting back a year:

Sales LY LFL = CALCULATE ( [Sales LY], 'Date'[Is Past] = TRUE () )

Sales vs LY % =
VAR SalesPriorYear = [Sales LY LFL]
RETURN
    DIVIDE ( [Total Sales] - SalesPriorYear, SalesPriorYear )

The outer filter trims the current period to the days that have data, say 1–27 September; SAMEPERIODLASTYEAR then shifts those dates to 1–27 September 2025. In retail, "like-for-like" usually also means excluding stores that were not open in both periods. That is a filter on your store dimension, not a date problem, and it belongs in its own measure.

How do you build a rolling average in DAX?

A rolling 12-month total uses DATESINPERIOD, which counts back from a start date by a number of intervals:

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

Microsoft's own example makes the behaviour clear: filtered to June 2020, a one-year window returns 1 July 2019 to 30 June 2020. Rolling 12 months is the most honest trend line for a seasonal business, because every point contains exactly one of each month.

For a rolling average, the common shortcut is to divide that total by 12 or 3. It is wrong at the start of your data, where fewer months exist. Average the months that are actually there:

Sales 3M Avg =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Total Sales] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

VALUES ( 'Date'[Year Month] ) lists the months in the window, and AVERAGEX averages monthly sales across them. This is where an unmarked date table bites: without it, the visual's filter on Year Month survives and the "three-month average" is just this month.

Which function answers which question?

Question	Measure pattern	Function
How are we doing this financial year?	TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )	TOTALYTD
This month so far?	TOTALMTD ( [Total Sales], 'Date'[Date] )	TOTALMTD
What did the same period do last year?	CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )	SAMEPERIODLASTYEAR
Versus last month?	CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )	DATEADD
Fair comparison part-way through a month?	Filter to 'Date'[Is Past], then shift	SAMEPERIODLASTYEAR + a flag column
What is the underlying trend?	DATESINPERIOD over 12 months	DATESINPERIOD
Smoothed monthly average?	AVERAGEX over months in a DATESINPERIOD window	AVERAGEX + DATESINPERIOD

Should you use the new calendar-based time intelligence?

Microsoft added calendar-based time intelligence in the September 2025 release, and as of September 2026 its time-based calculations page still labels it a preview and recommends it for new work. Instead of a date column, functions take a named calendar you define on the date table, so TOTALYTD ( [Total Sales], FiscalCalendar ) picks up the financial year from the calendar definition. Microsoft's TOTALYTD reference says you must not also pass a year-end date when using a calendar.

What it adds, per Microsoft and SQLBI's walkthrough:

• Non-Gregorian calendars. 4-4-5 retail calendars, ISO weeks and 13-period years, which the classic functions cannot express.
• Week-based calculations. TOTALWTD and a WEEK interval, available only with a calendar.
• Sparse dates. A complete date table is still recommended but no longer required.

The limitations listed by Microsoft are real ones for small teams: calendars cannot be authored in the Power BI Service, cannot be used with live-connected or composite models, and preview performance "isn't representative of the end product". You enable it in Power BI Desktop under File > Options and settings > Options > Preview features > Enhanced DAX Time Intelligence.

Our view: if you report on a calendar or April–March year, the classic functions on a marked date table do everything in this guide and are stable. Move to calendars when you need weeks or a retail calendar, and test the numbers against your existing measures before you switch a live report.

How do you check a time intelligence measure?

1. Reconcile one year-to-date figure to the management accounts for a closed month.
2. Put the measure in a table by month with the base measure beside it. YTD should rise by exactly each month's sales.
3. Check the first months of data. Rolling averages and prior-year comparisons are least reliable where history starts.
4. Look at the current, partial month with and without the LFL filter, so you know which one the report is showing.

Once you write these daily, our DAX keyboard shortcuts save real time. If your reports carry a dozen near-identical YTD and last-year measures nobody trusts, that is usually a model problem, and it is the kind of rebuild our Power BI consultancy takes on as part of the wider data and analytics practice.

Frequently asked questions

What is time intelligence in DAX?

Time intelligence is the set of DAX functions that change which dates a measure looks at: year to date, the same period last year, the previous month, or a rolling window. You write one measure, such as Total Sales, then wrap it in CALCULATE with a time intelligence function to get each variant.

Do I need a date table for DAX time intelligence?

For the classic functions, yes. Microsoft's documentation says you must mark a date table when you use them, and the date column has to hold unique, non-blank, contiguous dates. The newer calendar-based time intelligence, in preview as of September 2026, still recommends a complete date table but no longer requires one.

How do I calculate a UK financial year to date in DAX?

Pass a year-end date as the last argument of TOTALYTD. The default is 31 December; for an April to March year use a year end of 31 March, written in the month/day format Microsoft recommends, for example TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" ). The year part is ignored.

What is the difference between SAMEPERIODLASTYEAR and DATEADD?

For a one-year shift there is none: Microsoft documents that SAMEPERIODLASTYEAR returns the same dates as DATEADD with -1 and YEAR. DATEADD is more general, because it can shift by any number of days, months, quarters or years, backwards or forwards.

Why does my year-to-date line stay flat into future months?

Because your date table runs past the last day with data, and a year-to-date total for a future month is simply the total so far. Return BLANK for dates after your last transaction and the line stops where the data stops.

---

Easy Insight is a UK consultancy for AI, web, apps and data — senior specialists only, no juniors.