Exponential Moving Average - EMA - in PowerPivot


  • Hello All

    We´re having a challenge trying to put the [20] (trading days) Exponential Moving Average (EMA) in our data model in PowerPivot.


    Here is the EMA formula and sample spreadsheet:

    Pasted the formula here for convenience:

    SMA (simple moving average): [10] period sum / [10]
    Multiplier: 2 / ([Time period] + 1) )
    EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day).

    Sample spreadsheet


    In our model the fact table have these columns:
    Symbol Date Open High Low Close Volume

    And in the Calendar table we´ve identified the trading days (Calendar[TradingDayNumber]) as 1 so we can count them back.

    We want the EMA calculated field in a pivot table like this

    Filter context: a date selected on Calendar[FullDate]
    Row context: FactTable[Symbol]
    Values: FactTable[Close]
        FactTable[EMA 20D] >> Missing

    So far we got these calculated fields:


    Ema Multiplier
    =2 / ([Period] + 1)

    Average 20D CLOSE
    =IF([Sum of CLOSE]=BLANK(),BLANK(),(CALCULATE(AVERAGE(FactTable[CLOSE]),FILTER(ALL(Calendar),Calendar[TradingDayNumber]<=MAX(Calendar[TradingDayNumber]) && Calendar[TradingDayNumber]>MAX(Calendar[TradingDayNumber])-[Period]))))

    But it looks like the EMA formula contains a self reference from previous values, and it also starts from a SMA (Average 20D CLOSE) value.

    How can we do it?

    Thanks in advance. I highly appreciate your support.
    Saturday, July 19, 2014 9:42 PM


All replies