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

 

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),
    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.

 

 

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.

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

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