cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
moesteez Frequent Visitor
Frequent Visitor

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

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

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





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

1 REPLY 1
Super User IV
Super User IV

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

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





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors