cancel
Showing results for 
Search instead for 
Did you mean: 
Fowmy

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.


Link: https://finance.yahoo.com/quote/MSFT/history?p=MSFT

Fowmy_0-1624267437190.png

 

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

 

Fowmy_1-1624267452402.png

 

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

Fowmy_2-1624272889183.png

 

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.

Fowmy_3-1624272945404.png

 

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),
    Step2 = Table.AddColumn(
      Step1,
      "SMA",
      each
        if [Index] >= Days then
          List.Average(List.Range(Table.Column(Step1, DataColumn), _[Index] - Days, Days))
        else
          null,
      type number
    ),
    Step3 = Table.AddColumn(
      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.

 

Fowmy_4-1624273134939.png

 

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

 

Fowmy_5-1624273171610.png

 

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.

 

Fowmy_0-1624273470406.png

 

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.


Download the Power BI and the source data file attached below.

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


Comments

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 =
    ADDCOLUMNS (
        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