Forum Discussion
Moving Average over hierarchy (virtual table?)
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:
MA REGION =
VAR _mindate = MIN('Date'[Date])
VAR _table = ADDCOLUMNS(SUMMARIZE(Sheet1, 'Date'[Month Year], Sheet1[REGION]), "Sum Sales", CALCULATE(SUM(Sheet1[SALES]), REMOVEFILTERS(Sheet1[CITY])))
RETURN
CALCULATE(AVERAGEX(_table, [Sum Sales]), Sheet1[DATE] <= _mindate, REMOVEFILTERS('Date'[Month Year]))
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.
1 Reply
so i think i have figured it out but i don't really understand why it works...
MA =VAR _mindate = MIN('Date'[Date])VAR _table = ADDCOLUMNS(CALCULATETABLE(SUMMARIZE(Sheet1, 'Date'[Month Year], Sheet1[REGION]),'Date'[Month Year] <= _mindate),"sum", CALCULATE(SUM(Sheet1[SALES]), REMOVEFILTERS(Sheet1[CITY])))RETURNAVERAGEX(_table, [sum])I don't really understand why i don't have to removefilters on the 'Month Year' column such that I can get the values from previous months.can someone enlighten me?