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

[Anonymous]

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

[Ashish_Mathur]

Hi @Anonymous,

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

[TomMartens]

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

[Anonymous]

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.

[TomMartens]

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

Regards

Tom