Moving Average for 4 level indexed values

[Anonymous]

Hi, 

I struggled with it a lot, searched all over the forums, but could not achieve reasonable results - moving average for selling price is needed based on previous 3 rows results.

Please, take a look at the image, data with 4 level index, and after the last level, Product Name, change, the index is reset.

Tried these, and many similar, formulas, but in vain:

MA =
VAR m1=
SUMX(
FILTER(
_2017,
_2017[index2]>_2017[index2]-3 &&
_2017[index2]<=_2017[index2]+1),
_2017[SellingPrice])
VAR m2=
CALCULATE(
SUM(_2017[SellingPrice]),
FILTER(_2017,
SUMX(_2017,
EARLIER(_2017[index2])=_2017[index2] &&
EARLIER(_2017[index2])<=_2017[index2]
)))
RETURN m1

Would very much appreaciate any help. Thanks!

Best, 

Levan

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could try this formula:

Column = var a=[index2]-3
return CALCULATE(MAX(_2017[SellingPrice]),FILTER('_2017','_2017'[index2]=a))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ae3sor62pk3mhyt/Moving%20Average%20for%204%20%20level%20indexed%20values.pbix?dl=0

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

Thanks a lot! and sorry for delayed reply, thought nobody would answer it ever and stopped checking after awhile 🙂

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could try this formula:

Column = var a=[index2]-3
return CALCULATE(MAX(_2017[SellingPrice]),FILTER('_2017','_2017'[index2]=a))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ae3sor62pk3mhyt/Moving%20Average%20for%204%20%20level%20indexed%20values.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Anybody?