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
Newbie_2020
Frequent Visitor

Show Week Data for the last 8 weeks and display the average of the 8-weekly data

Hello everyone,

I need some help and I would really, really appreciate any inputs.

 

Desired Output: Dynamically show the weekly data for the last 8 weeks (including current selected week) and then calculate 8 week average based on the current selected week.

 

Example: If I select 3/12/2021 (Corresponds to Week 13), table will show the Sales on 3/21/21 and the weekly sales in the last 7 weeks.

 

Sample Output:

Desired PutputDesired Putput

 

I have 2 tables Sales_Fact and dimWeek joined by Closing Week.

 

Tables.PNG

What I did is create 8 different measures:

Current Wk, Last_Wk1, Last_Wk2, Last_Wk3, Last_Wk4, Last_Wk5, Last_Wk6, Last_Wk7

 

Last_Wk1 =
var selectedwk = max(dimWeek[Week_No])
var wk = calculate(selectedwk-1)
return
  calculate([NB_PL_CY],all(dimWeek[Week_No]),filter(all(dimWeek),dimWeek[Week_No]=wk))
 
Then I created Avg_8_Wk measure that average all those 8 measures.  This is the result:
 
 
Curren_SOlution.PNG
 
This shows the last 8 weeks Sales, however, I don't think this is the right way to do it and also I would like to be able to see the Week_Ending date that dynamically change instead of the measure name (Please see sample output above).
 
Would you be able to help? Thanks so much in advance!
 
Best,
Newbie_2020

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Newbie_2020 , Create a week rank in your week table (I am using date as a name here)

 

new column

Week Rank = RANKX(all('Date'),'Date'[Year End Date ],,ASC,Dense) //YYYYWW format

 

You can have a measure like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))


Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

But this will show rolling 8 weeks for last week, not a trend. For that, you need one more copy of the week table as an independent table.

refer: https://www.youtube.com/watch?v=duMSovyosXE

View solution in original post

2 REPLIES 2
Newbie_2020
Frequent Visitor

@amitchandak @ Thank you so much for the information.  The link you gave me really helped.  

 

This is what I did:

  • Created a new independent table with just the week ending.
  • Created a new measure Last_8_Wk_Sale (see screenshot below)

That worked exactly how I wanted 🙂  Now, the only thing I need to do is get the average of the weekly sales for the last 8 weeks.

 

Last8Wk.PNG

 
amitchandak
Super User
Super User

@Newbie_2020 , Create a week rank in your week table (I am using date as a name here)

 

new column

Week Rank = RANKX(all('Date'),'Date'[Year End Date ],,ASC,Dense) //YYYYWW format

 

You can have a measure like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))


Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

But this will show rolling 8 weeks for last week, not a trend. For that, you need one more copy of the week table as an independent table.

refer: https://www.youtube.com/watch?v=duMSovyosXE

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.

Top Solution Authors