How to Use Power BI Time Intelligence DAX Functions

What are time intelligence functions in DAX?

Time intelligence functions are a family of DAX functions that shift or extend the date filter on a measure, so one base measure can report year-to-date, the same period last year, or a rolling window without duplicating logic. The big four in practice are TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, and DATEADD.

They all follow the same idea: instead of changing what you sum, you change which dates the sum sees. Start with a base measure like Total Sales = SUM(Sales[Amount]), then wrap it so the date filter is rewritten — extended back to January 1 for YTD, or slid back twelve months for a prior-year comparison. If filter context is still new to you, read what DAX is (measures vs calculated columns) first; every function on this page is just a shortcut for rewriting filter context on a date column. Microsoft’s time intelligence function reference (opens in new tab) lists the full family and current syntax.

What does every time intelligence function require from your date table?

Every time intelligence function assumes a dedicated Date table that is continuous (no missing days), covers whole years, uses a true date data type, is related to your fact table, and is set with ‘Mark as Date Table’. Miss any of those and functions return blanks, wrong shifts, or totals that silently repeat.

This is the single most common failure point. Dates taken straight from the fact table usually have gaps — no sales on a Sunday means no row for that date — and DATEADD in particular refuses to shift over a calendar with holes. Build a Date table with CALENDAR() or CALENDARAUTO() (or reuse a shared one), make sure it spans complete years, relate it to the fact table’s date key, and mark it. Do it once and every function below simply works.

How do TOTALYTD and DATESYTD build year-to-date measures?

TOTALYTD is the one-line version: Sales YTD = TOTALYTD( [Total Sales], 'Date'[Date] ) accumulates from January 1 through the current date and resets each year. DATESYTD does the same date-shifting inside CALCULATE, which is the form you want when combining YTD with other filter changes.

The CALCULATE form is Sales YTD = CALCULATE( [Total Sales], DATESYTD( 'Date'[Date] ) ) — identical output, more room to grow. Both accept an optional fiscal year-end, so TOTALYTD( [Total Sales], 'Date'[Date], "6/30" ) gives a July–June fiscal year. Under the hood this is a running total that resets every January; if you want to understand or hand-build that mechanic, see our guide to running totals in Power BI with DAX, which walks the CALCULATE + FILTER + ALL pattern these shortcuts wrap.

What is the difference between SAMEPERIODLASTYEAR and DATEADD?

SAMEPERIODLASTYEAR shifts the current date range back exactly one year — nothing else. DATEADD is the general tool: it shifts by any number of days, months, quarters, or years, so it covers month-over-month and rolling comparisons too. SAMEPERIODLASTYEAR is literally DATEADD with a fixed one-year shift.

A prior-year measure looks like Sales PY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) ); the equivalent DATEADD form is CALCULATE( [Total Sales], DATEADD( 'Date'[Date], -1, YEAR ) ), and swapping the arguments to -1, MONTH gives month-over-month. With [Total Sales] and [Sales PY] in place, growth is just subtraction and DIVIDE. The table below summarizes when each function is the right pick.

Why do time intelligence measures return blank or wrong numbers?

Almost every broken time intelligence measure traces to the calendar, not the formula: the Date table is missing or unmarked, the calendar has gaps or partial years, the function filters the fact table’s date column instead of the Date table’s, or an inactive relationship never gets activated. Check those four before rewriting anything.

1. Is there a dedicated Date table with ‘Mark as Date Table’ set?
2. Is the calendar continuous and does it cover complete years?
3. Does the function reference 'Date'[Date] — the Date table’s column — not a fact-table date?
4. If the model has multiple date roles (order date, ship date), is USERELATIONSHIP activating the right one?
5. Is the visual’s axis built from the Date table, so the shift and the axis agree?

If the measures are right but the model still fights you — multiple calendars, role-playing dates, slow year-over-year visuals — that is model design work, and our data modeling and DAX service covers exactly that: we scope the fix, quote hours before working, and you approve before anything is deducted.