Time Intelligence in DAX: YTD, MTD and Comparisons

Year to date, month to date, same period last year and rolling averages are the most requested measures in business reporting. DAX has functions for all of them, but each depends on a proper date table and on understanding exactly which dates the function returns. This guide covers the essential functions and shows, with real numbers, a mistake that makes month-to-date comparisons look far worse than they are.

First, the date table

Time intelligence functions work on a date table: one row per date, unique dates, no blanks, no missing days, and full years. With classic time intelligence, the table must also be marked as a date table (Microsoft Learn: date tables). Power BI now also offers calendar-based time intelligence, in preview, which Microsoft recommends for performance and for calendars that are not simple January-to-December ones, such as retail week-based calendars (Microsoft Learn: time intelligence). The functions below are the classic ones, which remain the most widely used.

The core measures

Starting from a base measure, here total bike rentals:

Rentals = SUM ( Daily[cnt] )

Rentals MTD = TOTALMTD ( [Rentals], 'Date'[Date] )
Rentals YTD = TOTALYTD ( [Rentals], 'Date'[Date] )
Rentals Fiscal YTD = TOTALYTD ( [Rentals], 'Date'[Date], "3/31" )
Rentals PY = CALCULATE ( [Rentals], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Rentals PY via DATEADD = CALCULATE ( [Rentals], DATEADD ( 'Date'[Date], -1, YEAR ) )
Rentals Rolling 28 Days =
CALCULATE ( [Rentals], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -28, DAY ) )

• TOTALYTD evaluates the year-to-date value in the current context; its optional year-end argument, such as "3/31", handles fiscal years, and the text is read in the locale of the client (DAX reference: TOTALYTD).
• SAMEPERIODLASTYEAR returns the dates in the current context shifted back one year (DAX reference: SAMEPERIODLASTYEAR); DATEADD does the same with any interval, such as -1 MONTH.
• DATESINPERIOD returns a run of dates from a start date for a number of intervals, forwards or backwards (DAX reference: DATESINPERIOD), which makes rolling windows straightforward.

What each returns, with real numbers

Here are the date ranges these measures cover when the last date in context is 15 December 2012, summed from two years of daily bike rentals in Washington, D.C. (UCI: Bike Sharing):

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/275/bike+sharing+dataset.zip"
day = pd.read_csv(zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())).open("day.csv"), parse_dates=["dteday"])
rentals = day.set_index("dteday")["cnt"]

def total(start, end):                                   # the dates a time intelligence function returns
    return int(rentals[start:end].sum())

today = pd.Timestamp("2012-12-15")                       # the last date in the current filter context
last_year = today - pd.DateOffset(years=1)
measures = {
    "MTD (TOTALMTD)": total("2012-12-01", today),
    "PY MTD (SAMEPERIODLASTYEAR of MTD dates)": total("2011-12-01", last_year),
    "PY full month": total("2011-12-01", "2011-12-31"),
    "YTD (TOTALYTD)": total("2012-01-01", today),
    "PY YTD": total("2011-01-01", last_year),
    "Fiscal YTD, year ending 31 March": total("2012-04-01", today),
    "Rolling 28 days (DATESINPERIOD)": total(today - pd.Timedelta(days=27), today),
}
for name, value in measures.items():
    print(f"{name:<42}{value:>9,}")
print(f"MTD vs PY MTD: {measures['MTD (TOTALMTD)'] / measures['PY MTD (SAMEPERIODLASTYEAR of MTD dates)'] - 1:+.1%} | "
      f"MTD vs PY full month: {measures['MTD (TOTALMTD)'] / measures['PY full month'] - 1:+.1%}")

MTD (TOTALMTD)                               79,782
PY MTD (SAMEPERIODLASTYEAR of MTD dates)     49,033
PY full month                                87,323
YTD (TOTALYTD)                            2,005,645
PY YTD                                    1,204,813
Fiscal YTD, year ending 31 March          1,640,889
Rolling 28 days (DATESINPERIOD)             137,063
MTD vs PY MTD: +62.7% | MTD vs PY full month: -8.6%

The fiscal year-to-date figure starts on 1 April, so it is smaller than the calendar year to date, and the rolling window covers the 28 days ending on 15 December, crossing into November.

The partial-month trap

The last line is the important one. The first half of December 2012 had 62.7% more rentals than the first half of December 2011. Compared with the whole of December 2011, it appears to be 8.6% down. Both numbers are arithmetically correct; only the first is a fair comparison.

The trap appears when the date table runs to the end of the month or year, as a date table should. On 15 December, a visual showing December 2012 has all 31 December dates in its filter context, even though data exists only to the 15th. The month-to-date measure is unaffected, because future days simply have no data, but SAMEPERIODLASTYEAR shifts all 31 dates back and returns the whole of December 2011. The result is a partial month compared with a full one.

The fix is to shift only the dates that have data. A common pattern adds a column to the date table marking dates up to the last date in the fact table, and filters the dates before shifting them:

DateWithData = 'Date'[Date] <= MAX ( Daily[dteday] )    -- calculated column in the Date table

Rentals PY MTD =
CALCULATE (
    [Rentals MTD],
    CALCULATETABLE ( DATEADD ( 'Date'[Date], -1, YEAR ), 'Date'[DateWithData] = TRUE )
)

Test it by comparing the measure on a day when the month is incomplete against a manually calculated figure, exactly as the numbers above do. Time intelligence in analytics discusses partial periods and seasonality beyond DAX.

Quarters and rolling averages

Quarter to date follows the same pattern as month and year to date, with TOTALQTD or DATESQTD inside CALCULATE. A rolling total becomes a rolling average by dividing by the number of days that actually have data, rather than by a fixed 28, so the first weeks of the data, or a gap in it, do not drag the average down:

Rentals Rolling 28 Day Avg =
VAR Window = DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -28, DAY )
RETURN DIVIDE ( CALCULATE ( [Rentals], Window ), CALCULATE ( COUNTROWS ( Daily ), Window ) )

With one row per day in the daily table, the denominator counts the days in the window that have rentals recorded.

Year-over-year growth

With a correct prior-year measure, growth follows the usual variables pattern:

Rentals YoY % =
VAR Current = [Rentals]
VAR Prior = [Rentals PY]
RETURN IF ( NOT ISBLANK ( Prior ), DIVIDE ( Current - Prior, Prior ) )

The first year in the data has no prior year, so the measure returns blank rather than a misleading figure. DAX variables explains the pattern, and calculation groups show how to apply YTD, prior year and growth to every measure without writing each one repeatedly.

Common mistakes

• No proper date table, or relying on auto date/time for anything beyond simple exploration.
• Comparing partial and complete periods, as above.
• Fiscal year ends forgotten, so year to date restarts in January for a business whose year starts in April.
• Using the fact table's date column in time intelligence functions instead of the date table's.
• No test on a partial period, so the trap above is only discovered when a manager asks why the month looks so bad.

For measures and context in general, see the DAX guide. Time intelligence functions are part of the DAX module of the Power BI course.

Sources

• Design guidance for date tables in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Time intelligence in Power BI (Microsoft Learn) — Microsoft Learn
• TOTALYTD function (DAX reference, Microsoft Learn) — Microsoft Learn
• SAMEPERIODLASTYEAR function (DAX reference, Microsoft Learn) — Microsoft Learn
• DATESINPERIOD function (DAX reference, Microsoft Learn) — Microsoft Learn
• Bike Sharing (UCI Machine Learning Repository) — UCI Machine Learning Repository