Forum Discussion

FireFighter1017's avatar
FireFighter1017
Icon for Advocate III rankAdvocate III
3 years ago

DAX EMA Attempt

I was able to create a couple of measures to obtain it:

 

First you need a simple moving average for your 1st period.  I'm using a 8 period SMA here:

 

When used in the EMA DAX, it will pickup the right SMA (see measure [Reactive Hours SMA8] in variable "_EMAW8".

Now here's how it all comes together:

 

 

Reactive Hours EMA8 = 
	var _lastDate = CALCULATE(MAX('Calendar'[WeekEndDate]), FILTER('Calendar', 'Calendar'[WeekEndDate]<today()))
	var _N = 8
	var _smoothFactor = 2 / (_N + 1)
	
	var _EMAW8 = 
		var _days = _N * 7
	    return CALCULATE(
	        [Reactive Hours SMA8] + _smoothFactor * ([Sum of Reactive Work] - [Reactive Hours SMA8]),
	        FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    )
    
    var _EMAW7 = 
		var _days = (_N - 1) * 7
		var _prevEMA = _EMAW8
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	
	var _EMAW6 = 
		var _days = (_N - 2 ) * 7
		var _prevEMA = _EMAW7
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	
	var _EMAW5 = 
		var _days = (_N - 3 ) * 7
		var _prevEMA = _EMAW6
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	    	
	var _EMAW4 =
		var _days = (_N - 4 ) * 7
		var _prevEMA = _EMAW5
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	
	var _EMAW3 = 
		var _days = (_N - 5 ) * 7
		var _prevEMA = _EMAW4
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	
	var _EMAW2 = 
		var _days = (_N - 6 ) * 7
		var _prevEMA = _EMAW3
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)
	
	var _EMAW1 = 
		var _days = (_N - 7 ) * 7
		var _prevEMA = _EMAW2
	    return 
	    	CALCULATE(
	        	_prevEMA + _smoothFactor * ( [Sum of Reactive Work] - _prevEMA ),
	        	FILTER(ALL('Calendar'[WeekEndDate]), 'Calendar'[WeekEndDate] = _lastDate - _days)
	    	)

	return
        _EMAW1

 

 

Note that "_lastDate" variable return the week end date from last week with any date filter applied, so that wehn you put the measure in a line chart with a date axis, it will calculate the EMA for each date.

 

 

var _lastDate = CALCULATE(MAX('Calendar'[WeekEndDate]), FILTER('Calendar', 'Calendar'[WeekEndDate] < today()))

 

 

 Then every "_EMAWx" variable will calculate the EMA from the 8th week from now up to last week.

 

Now I did get differences from an Excel Spreadsheet calculation of the same data.  So I'm not entirely sure if it's accurate.  Any feedback woudl be appreciated

2 Replies