How to find last year sales where the weeks are different from "Sameperiodlastyear"

[Teglbrander]

Hi Guys

I'm struggleling with week comparison in PowerBI desktop.

The case is quite simple, but haven't found a solution so far:

I want to meassure my sales by weeks. I there created a date table to help me.

Sales by week number this year is going fine. 

Issue comes when comparing with last years sales. The week numbers I recieve for 2021 is not correct with my calender as every week seems to be one number higher than "normal" I.E week 7 is called week 8.

Furthermore I want to compare the weekly sales in this years week 53, with week 1 last year which will not work with the dax function "sameperiodlastyear". 

Therefore I created 2 weekly columns in my date table called "week number this year" and "week number last year".

Like this:

So now I want to create a meassure to show me my sales in same week last year, I.e sales in week 2020-53, will show sales from week 2020-01, 2020-52 from 2019-52 and so on.

The date table is connected with my bookintable on bookingdate=date.

How to make the meassure that will look at the week name this year, find the equal one in the column Year.WeekLY and sum colums "sales" in my bookingtable from that filter?. Tried a lot of filter / earlier etc. with no luck.

[amitchandak]

@Teglbrander , check my blog on

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

last year week is 52 week behind of 364 days behind

Last year Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-52))

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

[Teglbrander]

You helped me half way, but. Its not always 52 weeks behind. In 2020 there is 53 weeks, so you can't use the "-52" weeks formula. Suggestions?

[amitchandak]

@Teglbrander , Same wee day last is always 52 weeks or 364 days behind, 53 weeks is because of the way start the week. I talked week tank based on week start date so that it can work across the year

If not you can subtract a year from the date and fins week range . These files can help in setting up

https://www.dropbox.com/s/c8b8riz1ewuh2ei/WOW_WTD.pbix?dl=0

https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Teglbrander]

Thanks for your help.

instead of the "-52" value, I made another column in my date table showing the value to subtract. (Some weeks 52, some 53). That did the trick for me.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Teglbrander]

Sorry, I don't see how?.. Or maybe don't understand?. Could you explain how this helps me for example comparing week 2020-53, with 2020-1?