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.

[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.