Forum Discussion
Moving Average for 4 level indexed values
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
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.
Regards,
Daniel He
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
4 Replies
v-danhe-msftMicrosoft Employee
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.
Regards,
Daniel He
- v-danhe-msft
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