Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |