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, With the following data:
DATE | REGION | CITY | SALES | |||
01/01/2020 | NORTH | MANCHESTER | 6 | |||
01/01/2020 | NORTH | LIVERPOOL | 8 | |||
01/01/2020 | NORTH | NEWCASTLE | 4 | |||
01/02/2020 | NORTH | MANCHESTER | 6 | |||
01/02/2020 | NORTH | LIVERPOOL | 9 | |||
01/02/2020 | NORTH | NEWCASTLE | 6 | |||
01/03/2020 | NORTH | MANCHESTER | 0 | |||
01/03/2020 | NORTH | LIVERPOOL | 5 | |||
01/03/2020 | NORTH | NEWCASTLE | 4 |
With a date table with a date and start of month column (formatted and named as 'Month Year').
I am trying to show the moving average of total sales at region level. My expected output:
DATE | REGION | CITY | SALES | MOVING AVERAGE | ||||
01/01/2020 | NORTH | MANCHESTER | 6 | 18 | ||||
01/01/2020 | NORTH | LIVERPOOL | 8 | 18 | ||||
01/01/2020 | NORTH | NEWCASTLE | 4 | 18 | ||||
01/02/2020 | NORTH | MANCHESTER | 6 | 19.5 | ||||
01/02/2020 | NORTH | LIVERPOOL | 9 | 19.5 | ||||
01/02/2020 | NORTH | NEWCASTLE | 6 | 19.5 | ||||
01/03/2020 | NORTH | MANCHESTER | 0 | 16 | ||||
01/03/2020 | NORTH | LIVERPOOL | 5 | 16 | ||||
01/03/2020 | NORTH | NEWCASTLE | 4 | 16 |
JANUARY: 6+8+4 = 18
FEBRUARY: 6+9+6 = 21
Therefore average = 19.5 etc
My attempt is as follows:
My thinking is that I need to aggregate first and then average over time. I can aggregate and get the following output but am failing to get the moving average. I have tried a number combos of filters but haven't been able to crack it.
DATE | REGION | CITY | SALES | WRONG 😞 | ||||
01/01/2020 | NORTH | MANCHESTER | 6 | 18 | ||||
01/01/2020 | NORTH | LIVERPOOL | 8 | 18 | ||||
01/01/2020 | NORTH | NEWCASTLE | 4 | 18 | ||||
01/02/2020 | NORTH | MANCHESTER | 6 | 21 | ||||
01/02/2020 | NORTH | LIVERPOOL | 9 | 21 | ||||
01/02/2020 | NORTH | NEWCASTLE | 6 | 21 | ||||
01/03/2020 | NORTH | MANCHESTER | 0 | 9 | ||||
01/03/2020 | NORTH | LIVERPOOL | 5 | 9 | ||||
01/03/2020 | NORTH | NEWCASTLE | 4 | 9 |
Any help would be greatly appreciated.
so i think i have figured it out but i don't really understand why it works...
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |