Forum Discussion
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 calc...
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 )
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 )
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.