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
Solved! Go to 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
@amitchandak @ Thank you so much for the information. The link you gave me really helped.
This is what I did:
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.
@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
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!
User | Count |
---|---|
102 | |
36 | |
34 | |
29 | |
23 |
User | Count |
---|---|
121 | |
49 | |
45 | |
35 | |
21 |