SAMEPERIODLASTYEAR returns blank when the shifted prior-year dates produce no rows for the measure to aggregate. Check whether those dates exist in your Date table, whether that table actively filters the fact table, and whether the visual uses the same Date table—not a fact-date column or hidden auto date hierarchy.
The function does not search for the nearest previous data. It shifts the current date context back one year and returns a table of dates.
Start with a known-good measure
Assume this base measure:
Total Sales =
SUM ( Sales[Sales Amount] )The previous-year measure is:
Sales Previous Year =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)Microsoft documents SAMEPERIODLASTYEAR as returning dates shifted one year back from the dates in the current context. For classic date-column input, its result is equivalent to DATEADD ( dates, -1, YEAR ).
That definition gives you the debugging plan: inspect the current dates, inspect the shifted dates, and prove that the shifted dates filter Sales.
Check 1: Confirm prior-year data exists
If your fact table starts in 2025 and the visual shows 2025, there are no 2024 sales to return. A blank is correct.
Add these measures:
First Sales Date =
MIN ( Sales[Order Date] )Last Sales Date =
MAX ( Sales[Order Date] )Then compare them with the current visual period. Also inspect whether the previous year has matching business activity. A Date table can contain 2024 while Sales contains no 2024 rows.
Decide whether blank or zero is the truthful display. The BLANK versus zero guide explains why forcing + 0 changes meaning and visual density.
Check 2: Use a continuous Date table
Create one row for every calendar date across the full reporting range. The table should include the earliest required prior period, not only the dates found in the fact table.
A minimal calculated Date table might be:
Date =
CALENDAR (
DATE ( 2023, 1, 1 ),
DATE ( 2026, 12, 31 )
)In production, choose boundaries that cover the actual model and its comparison requirements. Add year, month, month number, quarter, and fiscal attributes as needed.
Mark the table as the model’s Date table when your model design requires classic time-intelligence behavior, and ensure the date column is unique, nonblank, and continuous.
A Date table built from MIN ( Sales[Order Date] ) to MAX ( Sales[Order Date] ) may omit the prior year you need for the first visible period.
Check 3: Use the Date-table field in the visual
Build the axis, rows, and slicers with fields from 'Date', such as:
'Date'[Year];'Date'[Month]; and'Date'[Date].
Do not mix 'Date'[Year] with Sales[Order Date] on the same diagnostic visual. A fact-table date column can create a context that the Date table does not receive in the expected direction.
Use:
Current Date Range =
FORMAT ( MIN ( 'Date'[Date] ), "yyyy-mm-dd" )
& " → "
& FORMAT ( MAX ( 'Date'[Date] ), "yyyy-mm-dd" )If this measure does not change with the visual row, the visual is not filtering the Date table you pass to SAMEPERIODLASTYEAR.
Check 4: Verify the active relationship
The common model path is:
Date[Date] (1) → Sales[Order Date] (*)The relationship should be active when [Total Sales] is meant to use order date.
Check:
- the relationship exists;
- both columns have compatible date data types;
Date[Date]contains unique dates;- timestamps in the fact key are not preventing matches; and
- filters flow from Date to Sales.
If the active relationship uses Order Date but the measure should report by Ship Date, activate that existing inactive relationship inside CALCULATE. The USERELATIONSHIP article shows the exact pattern.
Check 5: Count the shifted dates
Turn the table returned by SAMEPERIODLASTYEAR into evidence:
Previous Year Date Count =
COUNTROWS (
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)If the count is zero, inspect the current Date context and Date-table coverage.
If the count is positive but previous-year sales is blank, inspect the relationship and fact rows:
Previous Year Sales Rows =
CALCULATE (
COUNTROWS ( Sales ),
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)This separates a date-shifting problem from a no-matching-facts problem.
Check 6: Remove future and incomplete periods from the test
A calendar often extends beyond the latest fact date. If the visual includes future months, current sales and previous-year comparisons may produce combinations that look inconsistent.
Test one completed month with data in both years:
- filter to a single known month;
- display current sales;
- display previous-year sales;
- display current and shifted date counts; and
- compare the underlying rows.
Once that case works, expand to year-to-date and partial-period logic.
For fair partial-year comparisons, you may need to stop the prior-year period at the equivalent last loaded date rather than displaying a full previous month against an incomplete current month. That is a separate business rule from basic SAMEPERIODLASTYEAR.
Check 7: Avoid mixing auto date hierarchies
Power BI can create hidden auto date/time tables for date columns. A visual using an automatic hierarchy may not use the explicit 'Date' table referenced by your measure.
Use one deliberate Date dimension throughout the model:
- hide raw fact date keys from report authors where appropriate;
- build hierarchies on the Date table;
- use Date-table fields for slicers and axes; and
- reference the Date table in time-intelligence measures.
This makes the filter path visible and testable.
Check 8: Test fiscal and nonstandard calendars explicitly
Classic date-column time intelligence shifts Gregorian dates. If your business uses a 4-4-5 calendar, 13 periods, lunar years, or another custom calendar, “same period last year” may not mean “same dates shifted by one Gregorian year.”
Microsoft’s current documentation distinguishes classic and calendar-based time-intelligence behavior, including leap-day and 13-month calendar cases. Define the business period first, then choose the appropriate calendar design and shift.
Do not repair a fiscal-calendar mismatch by adding random days to the result.
A complete debug table
Create a temporary table visual with:
'Date'[Year];'Date'[Month];[Total Sales];[Sales Previous Year];[Previous Year Date Count]; and[Previous Year Sales Rows].
Read it from left to right:
- Did the visual create the expected current period?
- Did DAX produce shifted dates?
- Did those dates find fact rows?
- Did the base measure aggregate those rows?
That sequence usually finds the break faster than changing the time-intelligence function.
Frequently asked questions
Why is SAMEPERIODLASTYEAR blank for the first year?
The model has no earlier-year data for that period, or the Date table does not extend far enough backward. The blank is usually correct.
Should I replace blank with zero?
Only when zero means there were definitively no sales in a valid comparable period. If the period is unavailable, incomplete, or outside the data range, blank may communicate the truth better.
Can I use Sales[Order Date] directly?
An explicit Date dimension is more reliable for reporting, hierarchies, continuous periods, and multiple date roles. Use its date column in the function and its attributes on the visual.
How do I practice time-intelligence debugging?
Open a DAX Solver practice challenge, predict the current and prior date sets, then validate both before comparing the final measure.
