Working with SAMEPERIODLASTYEAR on a Day of Week-basis

[Anonymous]

Hi Community,

I once again need your expert guidance. I am working with sales volumes for a planning report. I succesfully used 

SAMEPERIODLASTYEAR to compare weekly sales volumes and it works fine on a calender week basis. Now I want to break it down further to the indicidual day.

In the given case, the current year is 2020, last year accordingly is 2019. Now, for an examplary Sunday May 10th, 2020, 

SAMEPERIODLASTYEAR assigns the value of Friday May 10th, 2019. However, this is hardly comparable and what I would be looking for is the reference of the analogous weekday, i.e. compare Sunday of Week 19, 2020 to Sunday of Week 19, 2019. Do you have any suggestions on how to do that?

Thanks and kind regards,

Steffen

[Anonymous · Accepted solution]

Thanks to both of you, I combined your two approaches for the current implementation I use. I already have a calendar table I could utilise and did the following:

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),FILTER(Dim_Date,Dim_Date[YEAR] = MAX(Dim_Date[YEAR])-1 && Dim_Date,Dim_Date[WEEKNUM] = MAX(Dim_Date[WEEKNUM]) && Dim_Date[WEEKDAY] = MAX(Dim_Date[WEEKDAY])))

It takes the Sum() for the same day of the same week one year earlier.

[v-deddai1-msft]

Hi @Anonymous ,

I would suggest you create a calendar table with weeknum and weekday columns:

Dim_Date = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2020,12,31)),"WEEKNUM",WEEKNUM([Date]),"WEEKDAY",WEEKDAY([Date],2))

Then you can get week day last year by the following measure:

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),FILTER(Dim_Date,Dim_Date[WEEKNUM] = MAX(Dim_Date[WEEKNUM&&Dim_Date[WEEKDAY] = MAX(Dim_Date[WEEKDAY])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , Same week day is last year 364 days behind. Try a measure like this with help from date table

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

Also refer Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8