Forum Discussion
Helper IIMy Day Week Year (DWY) formula needs improvement!
THE SITUATION:
Based upon good advice from Anonymous from this post I created a DWY column:
Then I use DWY to calculate sales difference between this year and same day of week last year. For example:
- Thursday, February 11, 2021 is DWY 50072021
- Same day last year, Thursday, February 13, 2020 is DWY 50072020
So to get last year sales I use:
- Thursday, December 31, 2020 is DWY 50532020
- Same day last year, Thursday, January 2, 2020 is DWY 50012020
THE PROBLEM: My DWY solution no longer works because LY Sales is looking for 50532019 but there is no 50532019.
WHAT I NEED: My DWY statement needs improved to create a unique number solution to account for weird calendar situations like December 31, 2020 vs January 2, 2020.
Any help would be appreciated.
Hi Quinnie2017 ,
You could try using the measure
Same Week Day Last year =CALCULATE([Sales], dateadd('Date'[Date],-364,Day))
Let me know if this worked for you.
Cheers
CheenuSing
7 Replies
Hi Quinnie2017 ,
You could try using the measure
Same Week Day Last year =CALCULATE([Sales], dateadd('Date'[Date],-364,Day))
Let me know if this worked for you.
Cheers
CheenuSing
Quinnie2017Perfect! I thought I might need to change it to this. And this works on a leap year too! Kudos - I will mark as solution and thanks again!
- danextian
Super User
hi Quinnie2017 , I am assuming that if the corresponding LY week does not exist it should return blank or zero. Have you tried wrapping LY sales with a conditional formula so if LY does not exisit the difference is just the current year?
- Quinnie2017
Unfortunately this would not be acceptable as there should always be LY sales in our dataset, thanks for the reply!
RobbiefJust ran into this one myself...... Did you come up with a solution for it?
