Moving Average over hierarchy (virtual table?)

[Anonymous]

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.

[Anonymous]

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])))

RETURN

AVERAGEX(_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?