DAX Time Intelligence

YTD, YoY, and period comparisons across calendar dimensions.

Syntax
TOTALYTD( Expr, Dates ), SAMEPERIODLASTYEAR( Dates ), DATEADD( ... )

Overview

Time Intelligence functions simplify temporal calculations: Year-to-Date, Month-over-Month, and moving averages.

Time intelligence requires a contiguous Date table without missing days marked as a Date table in Power BI. Functions like SAMEPERIODLASTYEAR shift active date filters back 12 months, allowing effortless period comparisons.

Revenue Prior Year = 
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR('Date'[Date])
)

When to use Time Intelligence

  • Year-to-date, quarter-to-date and month-to-date totals.
  • Comparisons with the same period last year and growth percentages.
  • Rolling periods such as the last 12 months.

Worked examples

Year-over-year growth

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

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

Build the prior-year measure once, then derive growth from it. DIVIDE returns blank instead of an error when last year has no sales.

Rolling 12 months

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

DATESINPERIOD returns the 12 months ending at the last visible date, giving a rolling total that smooths seasonality.

Common mistakes

  • Running time intelligence functions against Sales[Date] (fact table) instead of a dedicated contiguous Date table.
  • Date gaps breaking the internal VertiPaq calendar mapping.

Performance notes

Classic time-intelligence functions are efficient when they operate on a proper date table: one row per day, contiguous dates, marked as a date table and related to the fact table. Most performance and correctness problems come from using Auto Date/Time or a date column in the fact table instead of a dedicated Date table.

Frequently asked questions

Why does my time intelligence measure return blank?
Usually the date table is missing, not marked as a date table, has gaps, or the visual uses a date column from the fact table instead of the date table.
How do I handle a fiscal year?
Functions like TOTALYTD accept a year-end date argument (for example "6-30"). For more complex fiscal calendars, add fiscal columns to the date table and write the logic with CALCULATE and filters on those columns.
Should I turn off Auto Date/Time?
Yes, in most models. It creates a hidden date table per date column, wastes memory and gives inconsistent time intelligence. Use one shared date table instead.

Related DAX concepts