DAX Time Intelligence: YTD, MTD and Year-over-Year

DAX time intelligence functions let you calculate year-to-date (YTD), month-to-date (MTD) and year-over-year (YoY) numbers in Power BI with just a few lines of code. The secret is not the formulas themselves but a proper date table: once you have one, measures like TOTALYTD and SAMEPERIODLASTYEAR work almost like magic. This guide walks you through the setup and the most useful patterns, with copy-ready DAX.

What time intelligence means in DAX

Time intelligence is a family of DAX functions that shift or extend the dates in the current filter context. When a visual shows March 2026, a YTD measure quietly replaces "March 2026" with "January 1 to March 31, 2026" before summing. A prior-year measure replaces it with "March 2025". You write the measure once and it adapts to whatever slicer, row or column the user picks.

All of these functions are really shortcuts around CALCULATE, which changes the filter context. If CALCULATE still feels fuzzy, read our guide to the CALCULATE function first; the rest of this article will make much more sense.

Step 1: Build and mark a proper date table

Time intelligence functions expect a dedicated date table with these properties:

1. One row per day, with no gaps, covering full years (January 1 to December 31) for every year in your data.
2. A column of the Date data type with unique values.
3. A one-to-many relationship from that date column to the date column in your fact table (for example, Sales[OrderDate]).
4. The table is marked as a date table: select the table, then use Table tools > Mark as date table and pick the date column.

You can create the table in Power Query or with a DAX calculated table. Here is a simple DAX version:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

Sort the Month column by Month Number (Column tools > Sort by column) so months appear in calendar order instead of alphabetically. Then turn off the automatic date/time option in Power BI Desktop settings (Options > Data Load > Time intelligence) so you are not carrying hidden date tables for every date column.

This table sits at the center of a star schema. If your model is a single flat table, see how to model your data as a star schema before going further.

Step 2: YTD, QTD and MTD measures

Start with a simple base measure, then build period-to-date versions on top of it:

Total Sales = SUM ( Sales[Amount] )

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

Sales QTD = TOTALQTD ( [Total Sales], 'Date'[Date] )

Sales MTD = TOTALMTD ( [Total Sales], 'Date'[Date] )

Put Year and Month on the rows of a matrix and add these measures. Sales MTD will equal Total Sales at the month level (because the whole month is already selected), but it becomes useful on day-level visuals or cards filtered to a specific date. Sales YTD accumulates month after month and resets every January.

The CALCULATE equivalent and fiscal years

TOTALYTD is shorthand for CALCULATE plus DATESYTD. Knowing the long form helps when you need to add extra filters. Both accept an optional year-end date for fiscal calendars, written as a "month/day" string:

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

Sales Fiscal YTD =
TOTALYTD ( [Total Sales], 'Date'[Date], "6/30" )

With "6/30", the year resets every July 1, which matches a fiscal year ending June 30.

Step 3: Year-over-year comparisons

Year-over-year analysis is the question every manager asks: "Are we doing better than last year?" The core function is SAMEPERIODLASTYEAR, which shifts the current dates back exactly one year.

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

Sales YoY Change = [Total Sales] - [Sales PY]

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

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

Format Sales YoY % as a percentage. DIVIDE is safer than the / operator because it returns blank instead of an error when last year's sales are zero or blank, for example in the first year of data.

The last measure, Sales PY YTD, compares this year's running total to last year's running total for the same period. That is usually a fairer comparison than full last year versus a partial current year.

Step 4: Rolling periods and month-over-month

Not every question fits calendar boundaries. Rolling 12-month totals smooth out seasonality, and month-over-month views catch short-term changes:

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

Sales Prev Month =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

DATESINPERIOD starts from the last visible date and moves back 12 months. DATEADD is the flexible cousin of SAMEPERIODLASTYEAR: change the interval to DAY, MONTH, QUARTER or YEAR and the number to any positive or negative value.

Quick reference: which function to use

Common mistakes and how to fix them

• YTD shows the same value as the monthly total. You are probably slicing by the fact table's date column, or the date table is not marked and related. Check the relationship in Model view.
• Prior-year values are blank. The date table does not cover the earlier year, or your data simply starts later. Extend the CALENDAR range.
• Future months show a flat YTD line. Because the date table runs to December 31, YTD carries the last value into months that have no sales yet. A common fix is to wrap the measure: IF ( ISBLANK ( [Total Sales] ), BLANK (), [Sales YTD] ).
• Totals look wrong. At the grand total level, the "current period" is the entire visible range, so a YTD total equals the YTD for the last date in context. That is expected behavior, not a bug.

Microsoft keeps extending DAX, including newer calendar-based options for custom calendars such as 4-4-5 retail calendars. Check the official DAX reference on Microsoft Learn for the latest functions available in your version of Power BI.

Next steps

Build a date table in your own report today, mark it, and add the four core measures: Total Sales, Sales YTD, Sales PY and Sales YoY %. Put them in a matrix by year and month and check a few numbers by hand. Once those work, add a rolling 12-month line chart. If you are still new to measures in general, start with our DAX basics guide, then come back here to layer on time intelligence.

Watch: video lessons

▶

▶

Frequently asked questions