DAX Time Intelligence
YTD, YoY, and period comparisons across calendar dimensions.
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.