Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
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
Thanks a lot! and sorry for delayed reply, thought nobody would answer it ever and stopped checking after awhile 🙂
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
Anybody?
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |