DAX SAMEPERIODLASTYEAR Returns Blank: Date Table Requirements and Fix
SAMEPERIODLASTYEAR returns blank when the date column it receives is not a clean, contiguous, Date-type column that filters the fact table. The fix is a separate date table with one row per day, marked as the date table and related one-to-many to the fact table. The measure must then reference the date table’s column, never the fact table’s.
Root causes
SAMEPERIODLASTYEAR(dates) is shorthand for DATEADD(dates, -1, YEAR). It takes the dates visible in the current filter context, shifts them back one year, and returns that shifted set as a filter. If the shifted dates do not exist in the column, or do not match the fact table’s values, the measure evaluates over zero rows and returns blank.
Microsoft’s guidance for time-intelligence date tables lists the requirements:
- A column of Date (or Date/Time) data type
- Unique values with no blanks
- Contiguous dates with no gaps
- Full calendar or fiscal years, so the table starts on the first day of a year and ends on the last
- The table marked as a date table
Each failure produces a different symptom.
| Symptom | Technical cause | Fix |
|---|---|---|
| Whole measure is blank | The date argument is a fact-table column, or the date table is unrelated | Create a dedicated date table and relate it to the fact table |
| Blank for some days or months | The date column has gaps because days without transactions have no row | Generate one row per day in the date table |
| Prior-year value is always blank | The fact column holds date-times (2024-03-05 14:32), so values never match midnight dates | Convert the fact column to Date type |
| Error about contiguous dates | DATEADD received a non-contiguous column | Use a contiguous date table column |
| Wrong values, no error | Bi-directional or inverted relationship, or visuals slicing on the fact table’s date column | Set single direction from date table to fact table |
Missing or unmarked date table
Without a dedicated date table, a measure passes Sales[OrderDate] to the time-intelligence function. That column contains only the dates on which a sale occurred, so a shifted date can land on a day that has no row. Power BI’s auto date/time feature builds hidden date tables per column, but they do not apply when the report slices on a custom table.
Marking a table as a date table tells the engine which column drives time intelligence. It also makes Power BI clear any existing filters on the date table when a time-intelligence function runs.
Gaps in the date column or a date/time column used instead of date
Relationships match on exact values. A fact row stamped 2024-03-05 14:32:00 does not match 2024-03-05 00:00:00 in the date table, so that sale never filters through. Strip the time portion in Power Query with Date.From, which also reduces the column’s cardinality and the model size.
Gaps cause the opposite problem. A fact table with weekday-only sales has no rows for weekends, and a measure built on that column shifts onto dates that are missing.
Building a contiguous date table
M-code approach with List.Dates and marking it as a date table
Open the Power Query M-Code Generator and choose the date-table template. If you enter a start date of 2022-01-01, an end date of 2026-12-31, and the columns Year, Month Number, Month, and Year-Month, the output should look like this:
let
StartDate = #date(2022, 1, 1),
EndDate = #date(2026, 12, 31),
DayCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
ToTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
Typed = Table.TransformColumnTypes(ToTable, {{"Date", type date}}),
AddYear = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
AddMonthNum = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), Int64.Type),
AddMonth = Table.AddColumn(AddMonthNum, "Month", each Date.ToText([Date], "MMM"), type text),
AddYM = Table.AddColumn(AddMonth, "Year-Month", each Date.ToText([Date], "yyyy-MM"), type text)
in
AddYM
DayCount is 1,826 for this range (five years, including the 2024 leap day). List.Dates produces one entry per day, so gaps are impossible.
Name the query DimDate and load it. Then select the table, open Table tools, choose Mark as date table, and pick the Date column. Power BI validates that the column is unique and contiguous, and it rejects the marking if it is not.
Fix the fact table in the same session. If OrderDate holds date-times, add a typed Date column in the Sales query:
Table.AddColumn(Source, "Order Date", each Date.From([OrderDateTime]), type date)
Use Order Date for the relationship and remove or hide the original date-time column.
Relationship direction and cardinality
Create the relationship DimDate[Date] to Sales[Order Date] with these settings:
- Cardinality: one-to-many, with DimDate on the one side
- Cross-filter direction: single, from DimDate to Sales
- Active: yes, for the primary date
Bi-directional filtering lets the fact table filter the date table, which can shrink the date table to dates with sales and recreate the gap problem. If a second date such as Ship Date needs its own relationship, keep it inactive and activate it inside the measure:
Sales LY (Ship Date) =
CALCULATE(
[Sales],
USERELATIONSHIP(Sales[Ship Date], DimDate[Date]),
SAMEPERIODLASTYEAR(DimDate[Date])
)
Writing the measure
Open the Power BI DAX Query Builder, set the base measure to [Sales], the date column to DimDate[Date], and the pattern to prior-year comparison. It should generate:
Sales = SUM(Sales[Amount])
Sales LY =
CALCULATE(
[Sales],
SAMEPERIODLASTYEAR(DimDate[Date])
)
YoY % =
DIVIDE([Sales] - [Sales LY], [Sales LY])
DIVIDE returns blank instead of an error when the prior year is zero or blank. That is the correct result for the first year in the date table, which has no prior-year data.
The three common patterns behave differently:
| Function | What it returns | Use it when |
|---|---|---|
SAMEPERIODLASTYEAR(DimDate[Date]) | The selected dates shifted back one year | You want a standard year-over-year comparison |
DATEADD(DimDate[Date], -1, YEAR) | The same result as above, with a configurable interval (DAY, MONTH, QUARTER, YEAR) and count | You need rolling shifts such as -3 months or -1 quarter |
PARALLELPERIOD(DimDate[Date], -12, MONTH) | The full parallel period, with partial intervals filled out to complete months, quarters, or years | You need the whole prior period regardless of how much of the current one is selected |
The difference between DATEADD and PARALLELPERIOD matters for partial periods. With month-to-date selected, DATEADD returns the matching days of the prior-year month, while PARALLELPERIOD returns the whole prior-year month. Use DATEADD or SAMEPERIODLASTYEAR for like-for-like comparisons.
Testing with sample data
Use the Dummy Data Generator to build a dataset where the correct answer is known in advance. Generate one row per day from 2023-01-01 to 2024-12-31, with columns Order Date and Amount, and set Amount to a constant 100. Import it as the Sales table.
With a constant daily amount, every expected value is simple arithmetic:
| Selection in the visual | [Sales] | [Sales LY] | Why |
|---|---|---|---|
| Year 2024 | 36,600 | 36,500 | 2024 has 366 days, 2023 has 365 |
| Feb 2024 | 2,900 | 2,800 | 29 days against 28 days |
| 29 Feb 2024 (single day) | 100 | 100 | The shift lands on 28 Feb 2023, because 29 Feb 2023 does not exist |
| 1 Jan to 15 Mar 2024 | 7,500 | 7,400 | 31 + 29 + 15 days against 31 + 28 + 15 days |
| Year 2023 | 36,500 | 0 or blank | No 2022 data in the sales table |
For the February row, YoY % is (2,900 - 2,800) / 2,800 = 3.57%. If your visuals match this table, the date table, the relationship, and the measure all work.
Run a second test for gaps. Regenerate the data with weekdays only, then check that Sales LY for any month still returns values. A fact-table column would fail here, but DimDate still contains every day, so the shift always lands on a valid date.
Edge cases to watch
Slicing on the wrong column. Visuals must use DimDate[Year], DimDate[Month], and DimDate[Date], not columns from the Sales table. A fact-table date on an axis bypasses the date table, and the time-intelligence filter then has nothing to modify.
First year in the date table. Prior-year values for 2022 are blank because 2021 does not exist in the model. That is correct behavior, not a defect.
Date table extending past the data. A date table through 2026-12-31 with sales ending in October lets a “current year” selection include empty future days. Prior-year totals for the same selection cover real sales in November and December, so the comparison is uneven. Limit the measure to the last date with data, or filter the visual.
Auto date/time. Disable it under File > Options > Current File > Data Load. Hidden auto tables add model size and can conflict with the custom table.
Fiscal calendars. If the fiscal year does not start in January, SAMEPERIODLASTYEAR still shifts by 12 months, which is correct. For year-to-date totals, pass the fiscal year-end as the optional argument, for example DATESYTD(DimDate[Date], "6-30").
Final tip: add this check measure to a card visual with no filters applied. A result of 0 confirms that the date table is contiguous.
Date Gap Check =
DATEDIFF(MIN(DimDate[Date]), MAX(DimDate[Date]), DAY) + 1 - COUNTROWS(DimDate)
Frequently asked questions
Why does SAMEPERIODLASTYEAR return blank in Power BI?
It returns blank when the date column is not contiguous, is not a Date type, has no related prior-year rows, or the table is unmarked. Time intelligence needs a unique, gap-free date table related one-to-many to the fact table.
Do I need to mark a date table for SAMEPERIODLASTYEAR?
Yes, mark it. Marking identifies the date column for time intelligence and ensures filters on the date table are cleared correctly. Measures can still work unmarked in simple cases, but marking removes ambiguity and avoids blank results.
What is the difference between SAMEPERIODLASTYEAR and DATEADD?
SAMEPERIODLASTYEAR is shorthand for DATEADD with -1 YEAR. DATEADD also shifts by quarters, months, or days and by any interval count, so use it for rolling or non-annual comparisons. Both return a table of shifted dates.