In DAX, why does SAMEPERIODLASTYEAR return blank or wrong values without a proper date table?

## What a time-intelligence function actually returns Most DAX time-intelligence functions are table functions. `SAMEPERIODLASTYEAR ( 'Date'[Date] )` does not compute a number; it takes the set of dates currently visible, shifts each one back a year, and returns the resulting set. `DATEADD`, `DATESYTD`, `DATESINPERIOD`, `PARALLELPERIOD` and `PREVIOUSMONTH` all work the same way. You use them as a CALCULATE filter argument: ``` Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) ``` And because a table-valued filter on a date column overwrites the existing filter on that column, the measure evaluates over last year's dates instead of this year's. Understanding that these functions produce *date sets*, not numbers, explains every failure mode below. ## Requirement one: a real date dimension The column you pass must come from a table that behaves as a calendar: exactly one row per day, no gaps, no duplicate dates, covering complete calendar years from the first January before your earliest fact to the last December after your latest. The completeness matters because year-to-date and prior-year logic reason about whole years; a calendar that starts in March truncates the first year silently. The classic mistake is passing `Sales[OrderDate]` — the fact table's own date column. It contains only days on which something happened and repeats a date once per transaction. Shifting that set back a year gives you last year's *transaction days*, not last year's *period*, so a month with a few quiet days quietly reports a smaller comparison base. Some engines also reject a date column containing duplicates outright for certain functions, which at least fails loudly. ## Requirement two: the relationship The date table must be related to the fact table, normally one-to-many from `'Date'[Date]` to the fact's date key, with the filter flowing from date to fact. Time intelligence changes the filter on the date table; if nothing propagates that to the facts, the measure returns the same number as the unshifted one. When a fact table has several dates — order, ship, due — only one relationship can be active, and the others need `USERELATIONSHIP` inside CALCULATE to be used for a given measure. ## Requirement three: Mark as Date Table Marking a table as the model's date table does something specific: when a time-intelligence filter is applied to the marked date column, the engine removes the other filters on that table. Without it, a `'Date'[Year]` filter coming from a slicer or a matrix header can remain in force and intersect with the shifted dates, producing a blank — you asked for 2023 dates while a year filter still insists on 2024. Marking the table is also what makes it safe to turn off Power BI's Auto date/time, which otherwise generates a hidden per-column calendar that bloats the model and cannot express a fiscal year. ## Fiscal calendars and 4-4-5 Standard time intelligence assumes the Gregorian calendar. A fiscal year ending in June is partly handled — `DATESYTD` and `TOTALYTD` accept a year-end date argument such as `"6/30"` — but 4-4-5 retail calendars, 13-period calendars and ISO weeks are not expressible with these functions at all. The durable answer there is to put explicit period keys in the date table (period index, week index) and write the offset logic yourself with `CALCULATE` and `FILTER` over those keys. Interviewers like this because it separates people who have only used the wizard from people who have built a retail model. ## When blank is the correct answer Before debugging, check the data. A product launched this year has no sales a year ago, so `Sales LY` is genuinely blank and the growth percentage is genuinely undefined. Hiding that behind a zero makes a false comparison; usually the right move is `DIVIDE` returning blank and a visual that suppresses the row. Similarly, the earliest year in the model has no prior year at all, which is a data-range fact rather than a formula error. ## A debugging order that works Drop the shifted dates into a table visual to see what set is actually being returned; check that the date table is contiguous and complete; confirm it is marked as a date table; confirm the relationship is active and pointing the right way; confirm the measure filters the *date table's* column and not the fact's; and only then look at the measure logic. Nine times in ten the answer surfaces in the first three steps.