Covers DAX functions (TOTALYTD, DATEADD, SAMEPERIODLASTYEAR and others) that shift or accumulate filtered dates. Classic versions require a contiguous, marked date table; calendar-based versions, still in preview, rely on defined calendars.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Time intelligence in context, with comparison tables and the common traps.
Terms in this definition
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- TOTALYTD
Calculates year-to-date results for an expression from either a date column or a calendar. Fiscal years come from passing a year-end such as "6/30", an option calendars don't permit.
- DATEADD
Shifts the set of dates in the current filter context by a given number of years, quarters, months or days, earlier or later. Prior-year comparisons are a typical use of this DAX time intelligence function.
- SAMEPERIODLASTYEAR
Prior-year comparisons often wrap this inside CALCULATE: it takes whatever dates are currently filtered and moves them twelve months earlier, matching DATEADD with -1 YEAR.
- Date table
Lets you slice and group facts by period. It lists every calendar day of the years it covers exactly once, with nothing skipped, and classic DAX time intelligence only works after it has been marked as a date table.
Related terms
- Calendar-based time intelligence
A preview approach to DAX date maths: after switching on Enhanced DAX Time Intelligence, you set up calendars on a table through Calendar options and pass those, rather than a date column, to time functions. Fiscal, retail and week-based calendars are all handled.
- DATESINPERIOD
For rolling-period measures: given a starting date, a count and an interval, this DAX time intelligence function returns the matching span of dates, which normally extends backwards.
- DATESMTD
Month-to-date measures rely on this DAX time intelligence function, which yields each date between the first of the month and whatever latest date the current context holds.
- DATESQTD
Quarter-to-date measures rely on this DAX time intelligence function, which yields each date between the quarter's first day and whatever latest date the current context holds.
- DATESYTD
Year-to-date measures rely on this DAX time intelligence function. It yields each date from the year's first day up to whatever latest date the current context holds, and accepts an optional year-end date so fiscal years can be handled.
- FIRSTDATE
Given a date column or a calendar, this DAX time intelligence function returns the earliest date visible in context. Because the result is a single-row table, you can drop it in anywhere a date is expected.
- LASTDATE
A DAX time intelligence function that finds the latest date in context from a date column and returns it as a single-row table. It suits snapshot and closing-balance calculations, though the result is blank when that date has no data.
- MTD
Month to date: everything from the first of this month until today. In DAX you get it with DATESMTD or TOTALMTD, and calculation groups for time intelligence often include it.