Time Intelligence in DAX: YTD, MTD, and Rolling 12 Months — With Real Examples

Time comparisons are the most common calculations in any BI report. Year-to-date, month-over-month, rolling 12 months — here's how to write them correctly in DAX, what breaks them, and the one prerequisite that makes them all work.

🎯 Situation

After the date table article last month, the most common follow-up question was: 'OK, I have the date table. Now how do I actually write YTD, month-over-month, and rolling 12 months?' Three patterns. Four measures total. Here they are — with the exact DAX, the common mistakes, and why each pattern works the way it does.

👉 Time intelligence DAX has a prerequisite: a proper date table, marked as a date table, connected to your fact table. If you haven't built that yet, this article won't help — build the date table first (we covered this in Week 33). Once that's in place, these four measures take 20 minutes to write and will answer the most common time-based questions in any management report.

⚠️ Challenge

📅 The 4 measures every BI report needs

Revenue YTD — cumulative revenue from January 1st of the current year to the selected date
Revenue MTD — cumulative revenue from the 1st of the current month to the selected date
Revenue vs. Same Period Last Year — the selected period vs. the same period 12 months ago
Rolling 12 Months Revenue — the last 365 days from the selected date, always moving

❌ What breaks time intelligence

No date table (or not marked as date table) → all functions return blank or wrong values
Gaps in the date table → YTD accumulations reset or skip periods
Multiple date columns in the fact table connected to the same date table → ambiguous relationships
Date column in the fact table is a datetime type instead of date → filter context doesn't work correctly

🔍 Analysis

The 4 measures in DAX:

-- 1. Year-To-Date
Revenue YTD =
TOTALYTD([Total Revenue], DimDate[Date])

-- 2. Month-To-Date
Revenue MTD =
TOTALMTD([Total Revenue], DimDate[Date])

-- 3. Same Period Last Year
Revenue SPLY =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

-- 4. Rolling 12 Months
Revenue R12M =
CALCULATE(
    [Total Revenue],
    DATESINPERIOD(
        DimDate[Date],
        LASTDATE(DimDate[Date]),
        -12,
        MONTH
    )
)

-- Bonus: Year-over-Year growth %
YoY Growth % =
VAR CurrentYear = [Revenue YTD]
VAR PriorYear = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
IF(
    PriorYear <> 0,
    DIVIDE(CurrentYear - PriorYear, PriorYear),
    BLANK()
)

✓️ Best Practice

Three things to know about these measures:

1. TOTALYTD resets on January 1st — if your fiscal year doesn't start in January, use the fiscal year end parameter: TOTALYTD([Revenue], DimDate[Date], "06/30") for a June 30 fiscal year end.

2. SAMEPERIODLASTYEAR returns blank for dates with no comparison period — this is correct behavior, not a bug. A partial month in the current year will compare correctly to the same partial month last year.

3. Rolling 12 months is the most useful of the four for trend analysis — it smooths seasonality by always including a full year of data. Use it alongside MTD and YTD for a complete picture: what happened this month, this year, and over the trailing year.

💡 Summary

Four measures. Twenty minutes to write once the date table is in place. These four patterns cover the time-based questions in virtually every management report: how are we doing this year, this month, compared to last year, and over the last 12 months? Learn to write them from memory — they come up in every project.

👉 Time intelligence DAX isn't complicated. It just requires the right foundation.

Build the date table. Write these four measures. Every time comparison question answers itself.