Forum Discussion
Issue with SAMEPERIODLASTYEAR in Enhanced DAX Time Intelligence (Preview) Week 53 returns incorrect
Community SupportHi bpratyusha ,
Please try below two options for LY measure.
1. Your table is DimDate, but the Enhanced Calendar object is called Date(454). However OFFSET must always reference DimDate, NOT the name of the calendar object.
LY Sales =
CALCULATE(
SUM('fact'[sales]),
OFFSET(
-1,
ORDERBY(DimDate[fiscal_day_in_year]),
MATCHBY(DimDate[fiscal_year]),
DEFAULT
)
)
2. LY based on fiscal_year – 1 + same fiscal_day_in_year.
LY Sales =
VAR CurrentFiscalYear =
SELECTEDVALUE ( DimDate[fiscal_year] )
VAR CurrentFiscalDay =
SELECTEDVALUE ( DimDate[fiscal_day_in_year] )
VAR TargetFiscalYear =
CurrentFiscalYear - 1
RETURN
CALCULATE (
SUM ( Fact[sales] ),
KEEPFILTERS (
FILTER (
ALL ( DimDate ),
DimDate[fiscal_year] = TargetFiscalYear
&& DimDate[fiscal_day_in_year] = CurrentFiscalDay
)
)
)
Your DatePeriod slicer breaks LY because of bidirectional relationships. Use SINGLE-direction relationship instead of bidirectional relationships.
Cross filter direction, Single (DatePeriod --> DimDate)
DATEADD & SAMEPERIODLASTYEAR ARE NOT SUPPORTED for custom calendars. It is a limitation. We would recommend submitting your detailed feedback and ideas through Microsoft's official feedback channels, such as Microsoft Fabric Ideas. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.
https://ideas.fabric.microsoft.com/ideas/search-ideas/
Some members havesubmitted ideas regarding SAMEPERIODLASTYEAR function in ideas forum. Please refer below link, if any idea is related to your query please upvote the particluar idea.
Search - Microsoft Fabric Community
Thank you for being a valued member in Microsoft Fabric Community Forum
Regards,
Dinesh
Hello v-dineshya ,
Thank you for the recommendations. I tried to implemented yet I'm blocked on the issue.
1. LY Sales (offset) functionality dint really work. ORDERBY and MATCHBY is not recognizing DimDate table and its attributes. Also there is no DEFAULT.
2. LY based on fiscal_year – 1 + same fiscal_day_in_year. The recommended measure also dint work. It returned blank values.
The measure worked when KEEPFILTERS is removed.
3. Cross filter direction, Single (DatePeriod --> DimDate). I changed it to Single then tested it dint work
I'm not sure how to set the Signle direction of the filter from date period to dimDate.
I will submit an idea in the feedback channel so that the product team can work on the improvements.
Thanks,
Pratyusha
- v-dineshya
Hi bpratyusha ,
Thank you for the update. Once you submitted the idea, please share the link here would be helpful for other community members who may have similar feedback. Thank you for being part of the Microsoft Fabric Community.
Regards,
Dinesh