# 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

• Hi Ed

Yes, I renamed it and asked differently here:
Circular Dependency Error

Still trying to find a proper way to do it.

• Marked as answer by Thursday, August 07, 2014 7:34 PM
Sunday, August 03, 2014 7:32 PM

### All replies

• Nico,

Is this still an issue?

Thanks!

Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Saturday, August 02, 2014 2:37 AM
• Hi Ed

Yes, I renamed it and asked differently here:
Circular Dependency Error

Still trying to find a proper way to do it.

• Marked as answer by Thursday, August 07, 2014 7:34 PM
Sunday, August 03, 2014 7:32 PM