Before you read about a simple rolling average make sure you understand the fundamentals of static averages. Click the link below to learn about the different types of averages (yes there’s more than one!) and how to use them correctly.

Analysing Data: Different Types of Averages and How to Use Them Correctly

What is a Rolling Average?

Everyone is familiar with standard averages like the mean and median. They are great if you want to know the average score on a test or the average price of an item. However, these averages only give you a single value for a single point in time and struggle to answer questions which relate to comparing average over a set amount of time.

Instead of calculating the average of a single point in time a rolling average calculates an average over a period of time. This period of time could be days, weeks, months or even years – it all depends on the data and what you’re trying to answer from your data. The most likely scenario though is to calculate anywhere from 3-12month rolling average due to seasonality and sampling. What makes a rolling average so good at analyzing trends is that you continously re-calculate the average as time moves forward which allows you to compare previous averages and ascertain trends in your data.

Let me explain what I mean below

How do you calculate a rolling average?

As mentioned a rolling average can be calculated in a variety of different ways;

• Using different types of period i.e days, weeks, months, years…etc
• You can alter the number of period i.e 1,2,3,4,5…etc months
• You can also decide whether you can want to use data in past or in the future from your starting point (I’ll explain below! Bare with me!)

Let’s assume we have the data shown below. We can see that sales pick up on the lead up to christmas but drop down straight after with a spike in May & June. It’s hard to tell what our monthly average is and whether our revenue haas been increasing since January 2022. This is where rolling averages come in handy – let’s see how to calculate it.

1. Choose your period type and length

The first step is to workout what the best length and period type you should be using. For our example above it would be smart to use a 12-month rolling average because we want to know what our average monthly revenue is. Also, since our data is showing 12-month seasonality then it would be best to average over 12-months.

2. Find the earliest point in your data

Since we’ve chosen to use a 12-month rolling average then we need atleast 12 months of data. From our eample above this means that our earliest point would be December 2022 since it would need data from January 2022 – December 2022 (i.e 12months). Of course, if you have data going back 10 years but only want to work out the last 2 years that’s ok too. You don’t need to caalculate all 10 years for this to work!

A point to note here is that a rolling average will always include the period you are estimating it for. This means that if you’re working out a rolling aaverage for December 2022 then it must include data from December 2022.

3. Sum your values

Next we sum our values for each of the periods. Using the data above it would be;

1,500+1,600+1,800+1,500+3,000+2,500+1,200+1,000+1,800+2,200+3,000+3,500 = 24,600

4. Divide your summed value

Next we divide the sum of the values by the number of values. Essentially we’re working out the average for each of the months. For our dataset it would be;

24,600 / 12 = 2,050

Therefore, your 12-month rolliing average for December 2022 is 2,050

5. Repeat steps 3 & 4 for the next periods

Now all that is left is doing the same calculation for each of the periods i.e

January 2023 = (1,600+1,800+1,500+3,000+2,500+1,200+1,000+1,800+2,200+3,000+3,500+1600) / 12 = 2,058
February 2023 = (1,800+1,500+3,000+2,500+1,200+1,000+1,800+2,200+3,000+3,500+1600+1500) / 12 = 2,050
March 2023 = (1,500+3,000+2,500+1,200+1,000+1,800+2,200+3,000+3,500+1600+1500+1900) / 12 = 2,058
….etc

You can probably see now why it is called a ‘rolling’ or ‘moving’ average. Although it works similar to an average the calculation is constantly moving with the data and gives a trend instead of a single value. From our dataset the moving average would look like the below with the dark blue line our 12-month moving average.

From the moving average you can quickly tell that the revenue has been increasing slowly overtime with the 12-month average revenue on December 2022 equal to 2,050 whereas in December 2023 it increased to 2,158. Therefore, you can easily say that the trend is increasing eventhough there are times where revenue dips during quieter months.

How and when to use a Rolling Average?

Rolling averages are commonly used to analyse time series data to smooth out fluctuations and identify long-term trends. They can be used across all industries but are most commonly seen within finance, marketing & sales. Two main uses are:

1. When your data has seasonality

When data has a high amaount of seasonality it is usually a good choice to calculate rolling averages in order to identify overall trends. Seasonality tends to happen in company revenue and sales numbers as the lead up to Christmas is usually extremely lucrative. We saw an example of seasonality iin the example above.

2. When your data has a high variance

When data has a high variance, or fluctuates a lot, then using a rolling average will smooth the data and help identify trends which would otherwise by impossible to see. This is especially prominent in finance stock found in the stock market where data can vary on a minute, daily and weekly basis.

Learn to Calculate Rolling Averages on Power Bi

Learn how to calculate rolling averages on Power Bi by cliicking here

Exponential Rolling Averages

Technically what we’ve gone over in this post is known as ‘simple’ rolling/moving average. There is another type of moving average known as an ‘exponential’ moving/rolling average which works in a similar way but calculate the average in a slightly different way. However, 99% of the time you’ll be using the simple rolling average eventhough it’s name may suggest otherwise.

If you want to learn about exponential rolling average then click here!