Exponential Moving Average in Power Query (EMA)

[Fowmy]

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.

Exponential Moving Average in Power Query.pbix

[MikeAinOz]

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! 

[AlexisOlson]

@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.

[Fowmy]

@AlexisOlson 

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

Thanks 

[Anonymous]

Thank you for sharing this solution, @Fowmy, appreciate it. If I have a group, and interested in moving average by the group, how could I add that? Thank you in advance. 

[cam365]

Hi @Fowmy , I know it's been a while since you posted but like @Anonymous I am wondering if you know how to calculate EMA in a table with multiple stocks? Thanks in advance.

[plumek102]

@AlexisOlson  
Did you test your code?  I tried on my data but unfortunately doesn't work, or I am doing something wrong 🙂 
Would you be able to add PowerBi file? 

Piotr

[AlexisOlson]

@plumek102 Yes. Open up Fowmy's file, create a new measure, and paste in my code above.

[Ming_810]

Hi @Fowmy , thanks for your great code.

I have a small question: If I have a column contains multiple Stocks, then how to create EMA separately for each stock? There must be function with filter or list, I think.

Hope you can help on this.

[prirao0004]

i want to find out Std dev for 20 days for Bollinger bands, I'm getting 0.00 for all the values. Please help with this.  stddev = var c=STDEVX.P(TOPN(20,filter(all('WIPRO NS'[Index]),'WIPRO NS'[index]<=MAX('WIPRO NS'[Index]))), (SELECTEDVALUE('WIPRO NS'[Close]))) return c another formula i used was: stddev= var PeriodEnd = LASTDATE('WIPRO NS'[Date])   var PeriodStart= FIRSTDATE( DATESINPERIOD('WIPRO NS'[Date], PeriodEnd, -20, DAY))   var stddev = CALCULATE(stdev.p('WIPRO NS'[Close]),DATESBETWEEN ( 'WIPRO NS'[Date], PeriodStart, PeriodEnd )) return stddev but i'm getting 0 as the answer. also please help in finding avg-gain and avg loss values for rsi14 colums are as below: [date]  [Close]  [Index(from1)]  [PreviousvalueofClose]   [diff(close-prevclose)]   [if(diff>0,diff,0)=gain]    [if(diff<0,abs(diff),0)=loss]   excel formula goes this way:  for 14th index: average gain for 14days : first gain=sum of gain/14 average loss for 14days:first loss=sum of loss/14, then from 15th index , Avg. Gain is measured as ( (Prev Day Avg Gain * 13) + Current Day Gain)/14 Avg. loss is measured as  ((Prev Day Avg loss * 13) + Current Day loss)/14