Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all, please help me calculate the rolling 7 days moving average in a chart with rolling 30 day volume.
Below dax calculate correctly except for initial 7 days. Please advise how to ignore the rolling filter or do not show MA for first 7 days.
thanks for your help!
Solved! Go to Solution.
Hi @dhirendra59 ,
Try this measure
7 Days MA =
SWITCH(
TRUE(),
MAX('Calendar'[Date]) >= MINX(ALLSELECTED('Calendar'),'Calendar'[Date]) +7 ,
CALCULATE (
SUM( 'Cust Order Details'[Ordered Qty MT] )/7,
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-7,
DAY
))
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @dhirendra59 ,
Try this measure
7 Days MA =
SWITCH(
TRUE(),
MAX('Calendar'[Date]) >= MINX(ALLSELECTED('Calendar'),'Calendar'[Date]) +7 ,
CALCULATE (
SUM( 'Cust Order Details'[Ordered Qty MT] )/7,
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-7,
DAY
))
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hello @harshnathani - this DAX gave me same results as my old expression.
Possible to calculate the moving average based on historical data for 1st 7 days and ignore date filter?
HI @dhirendra59 ,
Click on
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@harshnathani - Thanks! My visual has filter which is relative date (last 30 days) and not user specified. It is working where I chose the date but I need to calculate the moving avegare in rolling 30 day chart.
Dear @dhirendra59 ,
Go and check this post , it will help you .
https://medium.com/@vivek.ranjan22/moving-average-using-dax-power-bi-413a31099091
Please dont forget to give kudos and also if this helps please accept as solution.
If you have doubt please let me know ill be happy to help[
regards
Sujit
@Sujit_Thakur thanks for your reply. I tried but still unable to get the expected results. Is it possible to calculate the 1st 7 day moving average based on historical data which is not visible in the chart?
see above chart
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |