Creating a Rolling Average Graph with Power BI

By:   |   Updated: 2018-08-24   |   Comments (1)   |   Related: More > Power BI

Problem

Can you describe a Rolling Average (also known as Moving Average) and how can I create a simple rolling average in Power BI without using DAX (Data Analysis Expressions)?

Solution

What is Rolling Average?

A rolling average also known as moving average is a statistical method that calculates the average of a data series for a given period. Rolling average has a wide usage. Especially in financial markets where traders or investors use this method to estimate stock prices. Also, rolling average assists to determine or estimate direction of trends and so it increases the right decision probability.  The main idea of this method is to determine trends and track the trend. Thus, it takes an advantage of avoiding daily changes and allows analysis of long term trends. Assuming that your data series has fluctuations, if you want to clarify these data series you can use a rolling average. Because it will provide smoothing of a data series and filtering noise data. After all these details we can dip into the calculation methodology for a rolling average.

Calculating a Simple Rolling Average

The below image illustrates the monthly consumption of electricity with a rolling average calculation. For this dataset we will calculate a three-month simple moving average.

sample data

The formula is very simple. Add the total values through the period and divide into number of periods.

  • Simple rolling average = (P1 + P2 + P3 + P4 + ... + Pn) / n

Simple rolling average point one calculation is like this

  • Sma1 = (39+42+40)/3
  • Sma1 = 40.33
  • Simple rolling average point two calculation is like this
  • Sma2 = (42+40+50)/3
  • Sma2 = 44

Now we will create a demonstration in Power BI with this data series. The below T-SQL code will create the sample data series in SQL Server.

DROP TABLE IF EXISTS TestRollingAvg
CREATE TABLE TestRollingAvg
(Id INT PRIMARY KEY IDENTITY(1,1),DtMonth DATE, ElectricityConsumption INT )
 
INSERT INTO TestRollingAvg VALUES('20120101',39)
INSERT INTO TestRollingAvg VALUES('20120201',42)
INSERT INTO TestRollingAvg VALUES('20120301',40)
INSERT INTO TestRollingAvg VALUES('20120401',50)
INSERT INTO TestRollingAvg VALUES('20120501',71)
INSERT INTO TestRollingAvg VALUES('20120601',60)
INSERT INTO TestRollingAvg VALUES('20120701',35)
INSERT INTO TestRollingAvg VALUES('20120801',39)
INSERT INTO TestRollingAvg VALUES('20120901',18)
INSERT INTO TestRollingAvg VALUES('20121001',56)
INSERT INTO TestRollingAvg VALUES('20121101',41)
INSERT INTO TestRollingAvg VALUES('20121201',13)

Now our data is ready for use. After this step we will connect SQL Server with Power BI. For this demonstration we can use Power BI Desktop edition. After installation we can find Power BI in the start-up menu.

Launch Power BI Desktop edition.

power bi

Click Get Data and select SQL Server.

power bi get data

Complete the connection string details and click Advanced options.

power bi connect to sql server

Write the following T-SQL code in the SQL Statement text box and click OK.

select * from TestRollingAvg
power bi connect to sql server

Select and fill security settings. If you use SQL Server authentication, you can select Database tab and fill database User name and password.

power bi connect to sql server

Click the Load button.

power bi load data

Drag and drop the Line and Stacked column chart into the Power BI design panel.

power bi visualizations

Drag and drop the DtMonth measure to the Shared axis and drag and drop the ElectricityConsumption measure to the Column values.

power bi visualizations

Click Expand down one level in the hierarchy button 3 or 4 times and the Line and stacked column chart will start to look like this. This option allows us to illustrate all levels of the date hierarchy values.

power bi graph

Remove the Day hierarchy because we don’t need this date hierarchy.

power bi visualizations

Right click over the DtMonth measure and select New quick measure.

power bi visualizations

Select Rolling average under the Time intelligence title.

power bi quick measures

Complete the Quick Measures window as shown below. Because we want to add the previous value and current value in the data set and find the average. For this reason, we set periods before value 2 and our period is based monthly so we select the period as month.

power bi quick measures

Drag and drop Average of ElectricityConsumption rolling average measure to the Line values.

rolling average graph

Right click in the chart and click show data. This option allows us to show values on the chart.

rolling average graph
rolling average graph

Change the graphic appearance in the format tab of graph.

power bi visualizations

Finally, the line and stacked column will look like the below image.

rolling average graph

If you look at the calculation background, Power BI automatically creates custom DAX code for this rolling average measure. When we click the Average of ElectricityConsumption rolling average measure, we can find the DAX code.

dax formula

Conclusion

In this tip, we explored the logic of rolling average and how to create a rolling average in Power BI.  Rolling average has several types (Exponential Moving Average (EMA), Smoothed Moving Average (SMMA), and Linear Weighted Moving Average) which differ from a simple rolling average and it has wide usage especially in financial analyses.  In this tip we created a rolling average in Power BI in the easiest method since we didn't have to use DAX.

Next Steps


Last Updated: 2018-08-24


get scripts

next tip button



About the author
MSSQLTips author Esat Erkec Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, August 24, 2018 - 10:09:50 AM - Pennyk Back To Top

This was very useful information and I loved the little tip about expanding the date hierarchy. I was really thrown the first time I used a date in powerbi and it aggregated to the highest level automatically.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools