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

1 ACCEPTED SOLUTION
Super User IV

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

Proud to be a Super User!

2 REPLIES 2
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.

Super User IV

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

Proud to be a Super User!

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks