Moving Averages in Power BI

Moving averages are well known statistical method to understand the data pattern and trend in the field of data analysis. Often it smoothen the line chart to give a proper direction of the trend. It is a well known tool in the stock market where analyst uses them draw insight on trend pattern, in which direction the trend would swing

---

How to achieve in Power BI

Well, there is no direct measure to give you moving averages. So, I decided to create one for my work purpose. And I am sharing this so that others can leverage the solution for free of cost

Step 1

From the larger dataset, I created a summarized dataset by date

Create a summary table

So you have the date and value field as 2 columns.

Step 2

Now I am going create a 3rd custom column as rankdate. Here I am going to simply rank the date in descending order i.e. older date will be given highest rank where as latest date will be ranked 1

Create a Rank column of Date

Step 3

Step 3 is creating the custom column for deriving the moving average you'd want. Using this DAX one can create any Moving average ( MA 20 or MA 50 or MA 100). You literally don't need Python script to embed in Power BI to get your MA

Average of 20 days of value in a rolling manner

Rolling Moving Average of 20 Days

As the dates roll over to next period, the moving average will recalculate itself as you can visualize from above example.

on 31/1/2020 - Moving Average comprises of 20 data points from 3/1/2020 - 31/1/2020. You can see that the dates are not continuous which is obvious as there cannot be value on everyday. But the concept one must understand is that when we say 20 days moving average, it means 20 data points. So, an average of values between these dates must be 301,252 [Red rectangular box]

similarly for 3/2/2020 Moving average comprises of 20 data points from 6/1/2020 - 3/2/2020. So, an average of values between these dates must be 305,739 which is actually average of these 20 data points [orange rectangular box]

similarly on 4/2/2020 Moving average comprises of 20 data points from 7/1/2020 - 4/2/2020. So, an average of values between these dates is equivalent to 288,169 which is actually average of these 20 data points [green rectangular box]

DAX used to create 20 days Moving average

I created a column and named it 20 days MA

20 Days MA = 
var rnk = LOOKUPVALUE(movingaavg[rankdate],movingaavg[Incoming_Date],movingaavg[Incoming_Date])
var ma =
if(movingaavg[rankdate]+20<=MAX(movingaavg[rankdate]),CALCULATE(AVERAGE(movingaavg[Deduction Amount]),FILTER(movingaavg,and(movingaavg[rankdate]<rnk+20,movingaavg[rankdate]>=rnk))),BLANK())
return ma

DAX used to create 50 days Moving average

with the same DAX you can create another column and only have to replace 20 by 50 get 50 days Moving Averages! Simple that

50 Days MA = 
var rnk = LOOKUPVALUE(movingaavg[rankdate],movingaavg[Incoming_Date],movingaavg[Incoming_Date])
var ma =
if(movingaavg[rankdate]+50<=MAX(movingaavg[rankdate]),CALCULATE(AVERAGE(movingaavg[Deduction Amount]),FILTER(movingaavg,and(movingaavg[rankdate]<rnk+50,movingaavg[rankdate]>=rnk))),BLANK())
return ma

End Result

I have plotted the data and below graph shows you the trend analysis of various moving averages. In stock market we use 20 days MA, 50 days MA and 100 days MA to understand the trend of a stock. like golden cross over, death cross over etc.

Moving Average Plotted as Line Chart