Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Working with SAMEPERIODLASTYEAR on a Day of Week-basis

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Community Support
Community Support

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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.