My Day Week Year (DWY) formula needs improvement!

[Quinnie2017]

THE SITUATION:

Based upon good advice from @CheenuSing from this post I created a DWY column:

DWY = WEEKDAY('Day Key'[Business Date])*10000000

+ WEEKNUM('Day Key'[Business Date])*10000

+ YEAR('Day Key'[Business Date])

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:

LY Sales = Calculate([CY Sales], Filter(ALL('Day Key'),'Day Key'[DWY] = MAX( 'Day Key'[DWY]) -1 ))

This works perfectly until I get to the following situation:

• 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.

Sample data here.

Any help would be appreciated.

[Robbief]

Just ran into this one myself......  Did you come up with a solution for it?

[CheenuSing]

HI @Robbief ,
The solution is in the thread itself. Check it out.

Cheers

CheenuSing

[Robbief]

O wow - This works for leap years too.....

[CheenuSing · Accepted solution]

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

[Quinnie2017]

Perfect!  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]

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!