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
