none
Exponential Moving Average - EMA - in PowerPivot

    Question

  • 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:
    http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:moving_averages

    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
    http://stockcharts.com/school/data/media/chart_school/technical_indicators_and_overlays/moving_averages/cs-movavg.xls

    ----

    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:

    Period
    =20

    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

Answers

All replies