Getting sales from the same week number(s) in the previous year

[moesteez]

Hi,

Ive got a sales report that compares weekly sales from one year to the previous year. Ive got a week number column in my calander that I filter my report by and when it comes to the DAX I have a few options.

Lets say I filter my report by week number 47 in 2019 - which is Monday November 18 to Sunday November 24, 2019.

For last years sales if I use 

CALCULATE([Sales], 'Calendar'[Year] = 2018)

I get the right result - Monday November 19 to Sunday November 25, 2018 (Monday to Sunday - Week 47 2018)

However this obviously breaks my report when the year changes over. So to future proof my report Ive looked at DateADD and SAMEPERIODLASTYEAR. ie:

CALCULATE([Sales], DATEADD('Calendar'[Date], -1, YEAR))

However Im finding that this is ignoring my week number filter and just doing a straight date swap - ie November 18 to Sunday November 24 2018 (which is Sunday to Saturday)

Does anyone know of a function that I can use so that I dont have to manually change the date filters in my DAX every year end?

Thanks

David

[amitchandak · Accepted solution]

Week is typically 364 days behind not year so try

CALCULATE([Sales], DATEADD('Calendar'[Date], -364, DAY))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin