Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Ashish_Mathur
Super User
Super User

Hi @Anonymous,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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.

 

 

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

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.