cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhirendra59
Helper I
Helper I

Simple 7 day moving average on rolling 30 days chart

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
)
)
1 ACCEPTED SOLUTION
harshnathani
Super User
Super User

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
    ))
)

 

 

1.jpg

 

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

View solution in original post

8 REPLIES 8
harshnathani
Super User
Super User

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
    ))
)

 

 

1.jpg

 

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

View solution in original post

@harshnathani Thank you!!! It worked!!

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 

 

1.jpg

 

 

 

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.img1.JPG

Sujit_Thakur
Solution Sage
Solution Sage

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?

dhirendra59
Helper I
Helper I

question2.JPG

 

see above chart

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!