Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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?