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

[Newbie_2020]

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 Putput

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

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:

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

[Newbie_2020]

@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.

[amitchandak · Accepted solution]

@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