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

Moving Average on 2 Measures

Hello everyone. I have a question on DAX to calculate moving average. Can I do moving average on 2 measures: 1 measure is sum of historical data calculation, the other measure is sum of forecast calculation? I know how to do moving average on them individually, but I'd like to combine them and do the moving average on both historical and forecast together.

 

Say I have some sales data and 2 measures such as below. The yellow highlighted, 3 Days MA Combined is the result I'm looking for. Any hint or advice is appreciated. 

DataSundowner_0-1658418045348.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope this can provide some ideas on how to create a solution for your data model.

 

Untitled.png

 

Moving avg 3days: = 
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _threedaysbefore = _currentdate - 2
VAR _condition =
    CALCULATE ( [Sales combine measure:], 'Calendar'[Date] = _threedaysbefore )
        <> BLANK ()
        && [Sales combine measure:] <> BLANK ()
RETURN
    AVERAGEX (
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, DAY ),
        [Sales combine measure:]
    )
        * DIVIDE ( _condition, _condition )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @DataSundowner,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Jihwan_Kim
Super User
Super User

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope this can provide some ideas on how to create a solution for your data model.

 

Untitled.png

 

Moving avg 3days: = 
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _threedaysbefore = _currentdate - 2
VAR _condition =
    CALCULATE ( [Sales combine measure:], 'Calendar'[Date] = _threedaysbefore )
        <> BLANK ()
        && [Sales combine measure:] <> BLANK ()
RETURN
    AVERAGEX (
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, DAY ),
        [Sales combine measure:]
    )
        * DIVIDE ( _condition, _condition )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you @Jihwan_Kim. The dax didn't work in my model, but your idea of adding up historical and forecast to combine them helped me a lot and and I figured out the rest. 

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.