Issue with SAMEPERIODLASTYEAR in Enhanced DAX Time Intelligence (Preview) Week 53 returns incorrect

[bpratyusha]

We are testing the new Enhanced DAX Time Intelligence (Preview) functionality in Microsoft Power BI Desktop which allows defining custom calendars (e.g., fiscal years, 4-5-4 retail calendars) directly in the data model.

Steps : 
1. Customized the calendar like below

2.  TY Sales = CALCULATE(sum('fact'[sales]))
3. LY sales = CALCULATE(sum('fact'[sales]), SAMEPERIODLASTYEAR('Date(454)') )

It should return null/ blank values for week 53 dates. 

Appreciate your help in resolving this issue.

[v-dineshya]

Hi @bpratyusha ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Ahmed-Elfeel , Thank you for your prompt response.

Hi @bpratyusha ,  Could you please try the proposed solution shared by  @Ahmed-Elfeel ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[bpratyusha]

Hello @v-dineshya , 

I have followed all the steps to work with Enhanced Time Intelligence feature according to the documentation https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-time-intelligence and defined the Retail calendar (pattern).

I have defined all the highlighted categories as shown in the picture.

Created the following measure to test this feature

LY sales (sameperiodly) = CALCULATE(sum('fact'[sales]), SAMEPERIODLASTYEAR('Date(454)') )

LY Sales (dateadd) = CALCULATE(sum('fact'[sales]), DATEADD('Date(454)' , -1, YEAR))

LY Sales corrected

When  SamePeriodLastYear is used the LY sales appear to same for all days in week 53

When DateAdd is used then week 53 it returns null values only bug is the highlighted LY sales in the image (in red) which is almost 6 times.

LY sales corrected values appears to be correct , however I don’t see value in Total column

Is there any bug with Sameperiodlastyear and DateAdd functionality even after the new Enhanced Time Intelligence feature is launched ? Please help me resolve the issue. 

[v-dineshya]

Hi @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

[bpratyusha]

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

[v-dineshya]

Hi @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

[bpratyusha]

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

[v-dineshya]

Hi @bpratyusha ,

We haven’t heard from you on the last response and was just checking back to see , Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing 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

[Ahmed-Elfeel]

Hi @bpratyusha,

Let me clarify this issue for you...this issue occurs because SAMEPERIODLASTYEAR uses a standard 365 day calendar while your fiscal calendar has 364-368 days and sometimes 53 weeks

So here is some Approaches to solve it you can try:

• Use Custom DAX with FILTER
  • Replace your LY sales measure with this pattern:

LY Sales Corrected = 
VAR CurrentFiscalYear = SELECTEDVALUE('Date(454)'[fiscal_year])
VAR CurrentFiscalDay = SELECTEDVALUE('Date(454)'[fiscal_day_in_year])
VAR PreviousFiscalYear = CurrentFiscalYear - 1
RETURN
CALCULATE(
    SUM('fact'[sales]),
    FILTER(
        ALL('Date(454)'),
        'Date(454)'[fiscal_year] = PreviousFiscalYear &&
        'Date(454)'[fiscal_day_in_year] = CurrentFiscalDay
    )
)

• Handle Week 53 Specifically

LY Sales Week53 Fixed = 
VAR CurrentDate = MAX('Date(454)'[calendar_date])
VAR CurrentFiscalYear = MAX('Date(454)'[fiscal_year])
VAR CurrentFiscalWeek = MAX('Date(454)'[fiscal_week_in_year])
VAR LYDate = SAMEPERIODLASTYEAR('Date(454)'[calendar_date])

RETURN
IF(
    CurrentFiscalWeek = 53 &&
    NOT EXISTS(
        FILTER(
            ALL('Date(454)'),
            'Date(454)'[fiscal_year] = CurrentFiscalYear - 1 &&
            'Date(454)'[fiscal_week_in_year] = 53
        )
    ),
    BLANK(),
    CALCULATE(SUM('fact'[sales]), LYDate)
)​

• Date Table Enhancement

  • Add a column to your date table to identify valid same-period-last-year dates:

// Add this as a calculated column to DimDate
Valid SPY Date = 
VAR CurrentFiscalYear = [fiscal_year]
VAR CurrentFiscalDay = [fiscal_day_in_year]
RETURN
CALCULATE(
    COUNTROWS('Date(454)'),
    FILTER(
        ALL('Date(454)'),
        'Date(454)'[fiscal_year] = CurrentFiscalYear - 1 &&
        'Date(454)'[fiscal_day_in_year] = CurrentFiscalDay
    )
) > 0

I suggest starting with First Solution as it's the most reliable for custom fiscal calendars. It directly matches fiscal days between years rather than relying on calendar date offsets.

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.