Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |