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

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

Anonymous — Thu, 05 Oct 2017 04:34:14 GMT

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

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

TomMartens — Thu, 05 Oct 2017 05:20:51 GMT

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

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

Anonymous — Thu, 12 Oct 2017 22:47:19 GMT

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.

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

TomMartens — Thu, 12 Oct 2017 23:19:41 GMT

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

Regards

Tom

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

Ashish_Mathur — Fri, 13 Oct 2017 00:14:16 GMT

Hi Anonymous,

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