cancel
Showing results for
Did you mean:
Highlighted
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

Thanks

4 REPLIES 4
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!
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

## 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

## 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?