why sameperiodlastyear() shift and how to fix?

[Joe_0365]

Hi, I have a problem I couldn't fix myself. Hope anyone can help me out.

My company uses a non-standard calendar. The rule is every period is 4 weeks, each year has 13 period, new year starts Jan 3, 2021, and a week starts Sunday.

Part of date table looks like this picture: 

Date table

The weekly sales data always ends Saturday. No daily sales data.
I am building up this year sales (2022) and last year sales (2021),  2021 sales is using the following code,

last year sales $ = Calculate([sum of sales],sameperiodlastyear(tblDate[date]))

My problem is the weekly sales shift in 2022. Please see my screen shot 2021 and 2022, 

My date table says the date May 29, 2021 is W21 in 2021, it should be shown in W21 in 2022 for comparison. 

Why it shows in W22 and how can I move it up to W21? Thank you in advance!

20212022

[parry2k · Accepted solution]

@Joe_0365 what you are trying to achieve is not going to be solved by SAMEPERIODLASTYEAR, since this function just takes set of dates and goes back one year for the same set of dates. Although it is doing the correct calculation since dates are falling in different weeks, it means you cannot work with dates, you have to work with dates.

I can explain more here but better if you share pbix file, remove sensitive information before sharing and I will get you the solution. In the meantime, if you are interested, check out this video on SAMEPERIODLASTYEAR on my YT channel.  https://youtu.be/YSz-2pjUSIY

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[Joe_0365]

Thank you for sharing more explanation on sameperiodlastyear. You were correct, I've figure out the problem that was my ignorance on calendar table. The problem was fix as soon as I corrected the calendar table. 😉

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.