Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm not sure where to start with this issue. I would like to add a dynamic rolling 4 week column that changes based on the data filters.
Here is an example dataset:
Fiscal Week Number | Actual | Expected | Division | Sites | KPI | SKU |
1 | 1614 | 1038 | A | Zone1 | A | A |
2 | 4581 | 1012 | A | Zone1 | A | B |
3 | 3094 | 1347 | A | Zone1 | A | C |
4 | 4970 | 1072 | A | Zone1 | A | D |
5 | 1063 | 1562 | A | Zone1 | A | E |
1 | 4383 | 1177 | A | Zone1 | B | A |
2 | 2277 | 1021 | A | Zone1 | B | B |
3 | 619 | 1519 | A | Zone1 | B | C |
4 | 1707 | 1245 | A | Zone1 | B | D |
5 | 2875 | 1613 | A | Zone1 | B | E |
1 | 4290 | 1322 | A | Zone2 | C | A |
2 | 3580 | 1163 | A | Zone2 | C | B |
3 | 4998 | 1771 | A | Zone2 | C | C |
4 | 2065 | 1033 | A | Zone2 | C | D |
5 | 1701 | 1881 | A | Zone2 | C | E |
Here are the desired results:
When I don't use any filters: (Rolling 4 week average is calculated by taking week 4 data and go back 4 weeks and average the values so (8742+8711+10438+10287)/4 = 9544.5)
Fiscal Week Number | Sum of Actual | Rolling 4 Week Avg |
1 | 10287 | |
2 | 10438 | |
3 | 8711 | |
4 | 8742 | 9544.5 |
5 | 5639 | 8382.5 |
When I use one filter (Sites = Zone1)
Fiscal Week Number | Sum of Actual | Rolling 4 Weeks |
1 | 5997 | |
2 | 6858 | |
3 | 3713 | |
4 | 6677 | 5811.25 |
5 | 3938 | 5296.5 |
When I use two fitlers (Sites = Zone1 and KPI = A)
Fiscal Week Number | Sum of Actual | Rolling 4 Weeks |
1 | 1614 | |
2 | 4581 | |
3 | 3094 | |
4 | 4970 | 3564.75 |
5 | 1063 | 3427 |
I've added a sample .pbix file and the relevant excel for this issue.
Thanks for the help!
Solved! Go to Solution.
hi, @Anonymous
You could use this formula to create a measure:
Rolling 4 Weeks = VAR _table = CALCULATETABLE ( VALUES ( 'data'[Fiscal Week Number] ), FILTER ( ALLSELECTED ( 'data'[Fiscal Week Number] ), 'data'[Fiscal Week Number] >= MAX ( 'data'[Fiscal Week Number] ) - 3 && 'data'[Fiscal Week Number] <= MAX ( 'data'[Fiscal Week Number] ) ) ) RETURN IF(COUNTAX(_table,[Fiscal Week Number])=4,AVERAGEX ( _table, CALCULATE ( SUM ( 'data'[Actual] ) ) ))
Result:
Best Regards,
Lin
Hello @v-lili6-msft ,
Thanks for the file. I think I know the issue. I think as soon as SKU gets introduced into the big table with everything. You won't see a Rolling 4 Weeks because there are never more than 4 weeks consecutively for SKUs.
I'll test this formula out with my real file and see if it translates over. Thanks for following up. I'll mark done as soon as I test it with my actual work file.
Thanks!
hi, @Anonymous
You could use this formula to create a measure:
Rolling 4 Weeks = VAR _table = CALCULATETABLE ( VALUES ( 'data'[Fiscal Week Number] ), FILTER ( ALLSELECTED ( 'data'[Fiscal Week Number] ), 'data'[Fiscal Week Number] >= MAX ( 'data'[Fiscal Week Number] ) - 3 && 'data'[Fiscal Week Number] <= MAX ( 'data'[Fiscal Week Number] ) ) ) RETURN IF(COUNTAX(_table,[Fiscal Week Number])=4,AVERAGEX ( _table, CALCULATE ( SUM ( 'data'[Actual] ) ) ))
Result:
Best Regards,
Lin
HI @v-lili6-msft thank you for looking to tackle the problem. Can you attach your .pbix file with the solution? I tried using the measure you prescribed, but I wasn't able to replicate your solution that you showed in your screen shots.
Hello @v-lili6-msft ,
Thanks for the file. I think I know the issue. I think as soon as SKU gets introduced into the big table with everything. You won't see a Rolling 4 Weeks because there are never more than 4 weeks consecutively for SKUs.
I'll test this formula out with my real file and see if it translates over. Thanks for following up. I'll mark done as soon as I test it with my actual work file.
Thanks!
Hi,
Do you have a Date column in your dataset? If yes, then the problem becomes fairly easy to solve.
Hello there is no date column. I agree if there was a date column it would be easier to solve, but I'm only work with week numbers. I tried joining to a date table, but using the built in rolling average measure doesn't help solve the issue that comes with the multi leveled filters.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |