Forum Discussion

DataSundowner's avatar
DataSundowner
Helper II
4 years ago
Solved

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. 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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.

     

     

    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 )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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.

     

     

    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 )

     

    • DataSundowner's avatar
      DataSundowner
      Helper II
      4 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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