Forum Discussion
Moving Average without dates
Hi, I need to calculate simple moving average for certain dates that do not come into succession, just an average of previous 3 figures, then for next figures, etc.. With date formatted column DA...
HI Anonymous
One approach is to add an Index column and then use that to generate your "last three items" average. I have attached a PBIX file (you can hide the Index col if you like).
The index calculated column can be added like this
Index = RANKX('Table1','Table1'[Date],,ASC)With the moving average column using it in this way
Moving Average = VAR MyIndex = Table1[Index] VAR myResult = AVERAGEX( FILTER( 'Table1', 'Table1'[Index] > MyIndex-3 && 'Table1'[Index] <= MyIndex ),'Table1'[Value] ) RETURN FIXED(myResult,2)
HI Anonymous
One approach is to add an Index column and then use that to generate your "last three items" average. I have attached a PBIX file (you can hide the Index col if you like).
The index calculated column can be added like this
Index = RANKX('Table1','Table1'[Date],,ASC) With the moving average column using it in this way
Moving Average =
VAR MyIndex = Table1[Index]
VAR myResult =
AVERAGEX(
FILTER(
'Table1',
'Table1'[Index] > MyIndex-3 &&
'Table1'[Index] <= MyIndex
),'Table1'[Value]
)
RETURN FIXED(myResult,2) 