How to create Rolling average in Power BI without data tailing off in the end

Today, I will talk about how to do rolling average in Power BI and how to address the tailing off in the end.

Rolling average is a technique used to address the short term volatility and fluctuation of data. In this example, I used the Apple Mobility Data for the state of New York. The data is from 1st March 2020 to 14th June 2020, which is the latest date when the data is available as of this writing. My dataset has only two columns: date and mobility. Here is what it looks like in Power BI

Apple Mobility for 11th and 12th May 2020 is not available, but you don’t see on the graph above because I smoothed it out by setting the date field to “continuous”.

To create a rolling average in Power BI, it’s actually quite simple. Click on “New Quick Measure” and choose “Rolling Average”, then you just need to fill in the details

Depending on what you are trying to do, you can set an appropriate period as well as the “periods before” and “periods after” fields. In this example, I am looking at 30-day rolling average. Therefore, I set it up that way.

Your data will look like this. There are a few things worth pointing out:

  • To make a new quick measure work, the time variable on the X-axis has to be a date hierarchy
  • That’s why you no longer see the whole graph on the screen. Instead, to see every data point, you now have to scroll horizontally
  • Even though the data stops on 14th June 2020, the graph doesn’t stop until 14th July 2020. It is because right now, you don’t tell Power BI when to stop projecting the data. The value on 14th July 2020 is exactly the value of 14th June 2020 and the upward trend is misleading because the more the graph moves to the right, the fewer data points there are.

If you look at the code for the New Quick Measure, here is what it looks like

To fix the tailing off issue, you just need to modify the code a little bit in the “Return” part, as follows:

The result will look like this

You can see the original data with its own short-term fluctuation in orange and the 30-day rolling average in the blue line. After the code is modified, the blue line now stops on 14th June 2020.

To enhance user experience by eliminating the need to roll horizontally, make sure that the date hierarchy also has “Year” as you can see below

Don’t you think the line chart looks smoother and better now?

Hope this little tutorial helps