Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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)

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.