cancel
Showing results for
Did you mean:

## Exponential Moving Average in Power Query (EMA)

An exponential moving average (EMA) is a type of moving average (MA) that gives a higher weight and importance on the latest data points. An exponentially weighted moving average responds more greatly to recent price changes than a simple moving average (SMA), which uses an equal weight to all observations in the period.

Though there are various methods by which the EMA is calculated, I will be sharing a method that I used to solve a question in the Power BI Community. The challenge in this solution is the recursive operation that refers to the previous value (EMA) on the same column.

I used the MSFT stock data from Yahoo Finance to demonstrate the solution approach. You can adopt this solution with the necessary adjustments to suit your requirement.

Step 1:  Connect to the data source using the Web Connector. Yahoo Finance provides easy access to many stock data with various parameters which you can explore.

Step 2:  Clean up the data and keep the Date and the Close value columns

Step 3:  Now we can create a function to generate a table based on the above data to calculate and add the
SMA (7 days simple moving average) and the EMA ( exponential moving average).

First, let us see how the calculation goes in Excel to have a better understanding.
The formula for 7days is easy as we only need to take the past 7 days data and average it

But, the formula for EMA is a bit challenging as we are making reference to the previous EMA calculation on the same column. You can check the Excel version of the formula below. It's easy with Excel as we can simply reference the cell above.

Find below the function that I created to achieve the above result in Power Query.
There are three inputs to the function The Table where you have your date and the Column to calculate the SMA and EMA and the number of Days to average on.

``````(DataTable as table, DataColumn as text, Days as number ) as table =>
let
Step1 = Table.AddIndexColumn(DataTable, "Index", 0, 1, Int64.Type),
Step1,
"SMA",
each
if [Index] >= Days then
List.Average(List.Range(Table.Column(Step1, DataColumn), _[Index] - Days, Days))
else
null,
type number
),
Step2,
"EMA",
each
if [Index] >= Days then
let
start = List.First(List.RemoveNulls(Step2[SMA])),
vlist = List.Range(Table.Column(Step1, DataColumn), Days + 1, _[Index] - Days),
acc = List.Accumulate(
vlist,
start,
(state, current) => (current - state) * (2 / (1 + Days)) + state
)
in
if [Index] = Days then start else acc
else
null,
type number
)
in
Step3``````

Step 4: Once you have created the function,  invoke it providing the parameters as shown below.

You have generated the following table with the expected SMA and the EMA columns.

Step 5: Load Only the final table to the Power BI model and use a line chart to visualize the Close, SMA, and the EMA.

As you can see in the chart above, the EMA moves quicker with the market and reactionary to the actual Closing values when compared to the SMA. This is because it is giving more weight to the recent values.

One disadvantage in calculating this in Power Query is that it deprives the option of slicing dicing with other columns and setting days as dynamic parameters. I will be looking at building an EMA solution using DAX in my future blogs.

Hope you found this article useful and learned some new techniques as well.

That's very nice @Fowmy , I had a go at this myself a few years ago Mike Allen's Blog | EMA Calculations with Power Query, but I think your function is more elegant, In fact I think I'll use it!

@Fowmy, given the recursive nature of the EMA definition it's not straightforward to accomplish this in DAX. However, I have some experience refactoring recursive definitions (check out this thread) and thought you might like to see how I'd write this in DAX without recursion:

``````Exp Mov Avg =
VAR N = 7
VAR Alpha = 2 / ( N + 1 )
VAR i_min = CALCULATE ( MIN ( 'MSFT Stock'[Index] ), ALLSELECTED ( 'MSFT Stock' ) )
VAR SMA =
CALCULATE (
AVERAGE ( 'MSFT Stock'[Close] ),
ALLSELECTED ( 'MSFT Stock' ),
'MSFT Stock'[Index] < i_min + N
)
VAR i_curr = SELECTEDVALUE ( 'MSFT Stock'[Index] )
VAR SeriesLen = MAX ( i_curr - i_min - N + 1, 0 )
VAR Series =
GENERATESERIES ( 1, SeriesLen ),
"@Price",
IF (
[Value] = 1,
SMA / Alpha,
LOOKUPVALUE (
'MSFT Stock'[Close],
'MSFT Stock'[Index], i_min + N + [Value] - 1
)
)
)
RETURN
SUMX ( Series, Alpha * POWER ( 1 - Alpha, SeriesLen - [Value] ) * [@Price] )``````

You can easily parameterize N and/or alpha and allow the Date selection to be dynamic with this formulation. As it's written, with N = 7 and Alpha = 2 / (N + 1), it should agree with your M calculation when Date is not filtered.

@AlexisOlson

Interesting approach, I will surely have a look at it.

Thanks

Top Kudoed Posts
Latest Articles
Archives