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.
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
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 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |