Simple 7 day moving average on rolling 30 days chart

[dhirendra59]

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!

Moving_Average_7day =

CALCULATE (

SUM( 'Cust Order Details'[Ordered Qty MT] )/7,

DATESINPERIOD (

'Calendar'[Date],

LASTDATE ( 'Calendar'[Date]),

-7,

DAY

)

)

[harshnathani · Accepted 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)

[dhirendra59]

@harshnathani Thank you!!! It worked!!

[dhirendra59]

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?

[harshnathani]

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)

[dhirendra59]

@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.

[Sujit_Thakur]

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

[dhirendra59]

@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?

[dhirendra59]

see above chart