cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mazharmh Member
Member

Dax formula for caluclating last 5 week's sales average of previous year

Hi all,

 

I have to Dynamically calculate average sales for last 5 weeks last year , i.e

 

Current week = 40 and current year 2017

 so I want 

last year  5 weeks average = week 41(2016) to week37(2016)/ 5

same way every week I am able to dynamically caluclate average to compare with sales

 

 

Please advice me 

 

Thanks

4 REPLIES 4
Super User
Super User

Re: Dax formula for caluclating last 5 week's sales average of previous year

Hey,

 

I'm sorry, but I need a little more information how to calculate  the measure you require, can you please explain how you "find" the timeframe of the 5 weeks for the last year?

 

My assumption

  • Your data model contains a separate calendar table
  • Based on the current week, determine the data of the EoW-Day (EoW = End of Week)
  • A = Determine the week of EoW-Day last year = 41 
  • TF LY = B(A - 4) .. A = 37 .. 41 (TimeFrame Last Year = weeks 37 to 41 last year)

What is your expectation if the current week is the 2nd week of the year, would you expext, that the time frame

  • contains weeks (precisely days from 2 years ago)
  • contains just weeks (precisely complete weeks 7 days) from last year
  • contains just the days from the 1st of January Last year until EoW-Day Last Year

This is important information how to determine the measure  to calculates the average sales for the last 5 weeks last year.

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
mazharmh Member
Member

Re: Dax formula for caluclating last 5 week's sales average of previous year

Sorry for late reply, In my model I don't have Date Table. Secondly, I did not calculate the time frame for last year 5 weeks.

 

 

Super User
Super User

Re: Dax formula for caluclating last 5 week's sales average of previous year

Hey, can you please explain how you did define the timeframe for the 5 weeks last year?

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Dax formula for caluclating last 5 week's sales average of previous year

Hi @mazharmh,

 

Shouldn't last 5 weeks of the previous year be week36 to week40?