Time intelligence without a date table: the YoY that looks right and isn't
Why SAMEPERIODLASTYEAR and DATESYTD need a marked, contiguous date table, and the two silent errors you get without one.
6 min read · updated 2026-09-21
What the functions assume
Every DAX time-intelligence function (DATEADD, SAMEPERIODLASTYEAR, DATESYTD, DATESINPERIOD, PARALLELPERIOD) takes a set of dates and shifts it. It assumes the column it receives contains every day of every year with no gaps, and that filters on year and month come from that same table.
When the column is the transaction date in the fact table, or a YearMonth text key, the assumptions break in two ways.
The two silent errors
Missing days. If no sale happened on 29 February, DATEADD cannot find the day a year later and drops it. The previous-year total is short by exactly the sales of the missing days, which is small enough to go unnoticed and large enough to be wrong.
Filter mismatch. Slicing by DimDate[Year] while the measure shifts Sales[OrderDate] means the shifted dates are still filtered by the slicer's year. Last year's sales come back blank or equal to this year's depending on the relationship, and the YoY % shows -100 % or 0 %.
A related trap: comparing two DATESINPERIOD windows anchored on the same date with different lengths (-3 and -6 months). The six-month window contains the three-month one, so the 'previous quarter' includes the current quarter.
Sales previous 3M = VAR AnchorEnd = MAX ( DimDate[Date] ) VAR PrevEnd = EDATE ( AnchorEnd, -3 ) RETURN CALCULATE ( [Sales], DATESINPERIOD ( DimDate[Date], PrevEnd, -3, MONTH ) )
The fix is structural
Build one date table with one row per day from the first to the last date in any fact, mark it as a date table, relate every fact date column to it, and use only its columns in slicers and in time-intelligence functions. CALENDARAUTO is acceptable for a prototype; a Power Query date table with fiscal columns is better for anything that ships.
Never hardcode a year in a measure ('IF ( YEAR ( TODAY () ) = 2024 ...')). The audit flags these because they are correct on the day they are written and wrong on 1 January.
What the audit checks
The static audit knows whether a table is marked as date table and lists every measure that calls a time-intelligence function on a column that is not from it. It also detects overlapping period windows and hardcoded years, and the AI review explains which visuals show the wrong comparison.