Forum Discussion
Issue with SAMEPERIODLASTYEAR in Enhanced DAX Time Intelligence (Preview) Week 53 returns incorrect
Community SupportHi bpratyusha ,
SAMEPERIODLASTYEAR works by shifting the entire set of dates by one year, but it expects a continuous date range. In a 454 calendar, Week 53 is not always present in the previous year. When it tries to map Week 53 from the current year to last year, it often defaults to the last available date range, causing repeated values. This is why you see the same LY Sales for all days in Week 53.
DATEADD shifts each date individually by the specified interval like -1 YEAR.
If the shifted date doesn’t exist in your custom calendar like Week 53 last year, it returns BLANK. This is expected behavior for DATEADD with non-standard calendars.
Your corrected measure likely uses logic like IF(HASONEVALUE(...), ... , BLANK()) or similar row-level calculation. Totals disappear because the calculation doesn’t aggregate properly at higher levels like month or year.
No, this is not a bug. It’s a limitation of how these functions work with custom calendars. The new Enhanced Time Intelligence feature helps generate relationships and patterns, but it doesn’t change the fundamental behavior of SAMEPERIODLASTYEAR or DATEADD.
Please try below alternative workaround.
1. Create a mapping table for current year vs. last year weeks including Week 53 logic.
2. Use LOOKUPVALUE or TREATAS to map the correct week from last year.
3. Alternatively, use OFFSET-based functions introduced in Enhanced Time Intelligence like OFFSET(-1, YEAR), which are designed for irregular calendars.
Please refer below sample measure.
LY Sales (Offset) =
CALCULATE(
SUM('fact'[sales]),
OFFSET(-1, YEAR, ALL('Date(454)'))
)
Please refer below links.
SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn
Solved: SAMEPERIODLASTYEAR AND DATEADD DIFFERENT RESULTS F... - Microsoft Fabric Community
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hello v-dineshya ,
Thank for the detailed explanation.
As shown in the highlighted image, dateadd function worked correctly , it retuned null values. However the only issue with this it inflates the LY sales by 6 times as highlighted in the above image in red.
OFFSET functionality doesnt seem to work. Either it is not able to identify the YEAR parameter (Parameter is not correct type) and also it is not able to identiy 'Date(454)' table as it is mapped internally to DimDate.
In addition to this , I'm working on creating a DatePeriod slicer (LW, MTD, QTD, STD, WTD, YESTERDAY, YTD) that interacts with Date Range Slicer and Measures.
The DatePeriod Tabe has "Date" ,Period columns.
Relationship is defined as DatePeriod (Many) to One (DimDate) and cross filtered on both directions.
However when a particular period is selected , it doesnt return any values for LY Sales
The measure defintion remains same as in the above post. I used RemoveFilters to remove the context however it dint work. Could you please help me resolve this issue ?
Thanks,
Pratyusha