3. Developing Moving Averages

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

When you have data that fluctuates in a noisy way, it can be difficult to identify the underlying trend. Moving averages are commonly used to smooth out data, and let you view the long-term trends.

Summary

Moving Averages

  • A moving average produces an average value of a data series at a point in time, based on a present and past values
  • For example, with a 3month moving average, the moving average for May is the average of revenue in March, April, and May
  • It aims to remove the impact of short fluctuations in a data series, so you can focus on the longerterm trends
  • The appropriate length of the moving average should be determined by the business context and the data available

Creating a Moving Average measure

  • To account for the initial months of the dataset, our measure calculates the number of months in the relevant window (such as 3 months) actually have data
  • The average is then calculated by dividing the total revenue by the number of months that have data

Keyboard Shortcuts

  • Alt B, F – Create new measure or Manage Measures
  • Alt N, V – Create PivotTable
  • Alt N, SZ, C – Create PivotChart

Transcript

When conducting time series analysis, you'll often find that you have sharp spikes or dips in a series.

These are often noise and are not indicative of any particular trend. You may want to smooth out this volatility to look at the overall trend.

Our goal in this lesson, is to learn how to calculate moving averages or rolling averages as they're sometimes called. Now moving average is a time series where the value at a specific point is the average of several past or future values. Don't worry if you're a little confused here, it will make more sense once we've seen it in action.

Let's start, by calculating a three month moving average of revenue.

We'll create a new measure and call it Moving Average Revenue.

We want this measure to look at revenue for the current month, as well as two previous months.

We'll then divide the total revenue in that three month window by the number of months where we have revenue data.

We'll accomplish this using the Calculate function. The expression to Calculate will be a Divide function.

The numerator will be sum of revenue and the denominator will be a distinct count of the month field, in the 2016 Sales table.

Next, we'll define the filter condition.

We'll use the Dates in Period function to define our three month window.

This function takes four arguments. The first is the Dates column from the Dates table.

The second, is the Last Date function applied to this Dates column.

The third, is negative three and the fourth is month.

This Dates and Period function will identify a three month period from the last day in a given month and the Divide function finds the average revenue in this window, based on the number of months that we have revenue data for in the 2016 Sales table.

We'll close the formula, set the format of the measure to Currency, and set the symbol to US dollar sign.

We'll then select OK to create the measure.

To really see how this measure works, we'll create a pivot table and place it on a new sheet.

We'll then expand the Dates table, add the Date hierarchy, expand the Sales table and add both the Revenue field, and the Moving Average Revenue measure.

Finally, we'll expand the hierarchy in the table to look at monthly revenue.

The Moving Average Revenue value displays the average of the three most recent revenue figures.

For example, the moving average revenue for May is the average of revenue figures for March, April and May.

The exceptions to this are January, where the moving average's just the revenue for that month, and February where the moving average can only be calculated over two months.

Let's create a pivot chart to see this graphically.

We'll create the chart with the shortcut Alt + N +SZ + C and select a Line chart.

It's clear that the moving average line is smoother than the revenue line.

The revenue line has several single period spikes that are not sustained, while the moving average line, allows us to focus on the overall trend.

In this example, the value of the moving average is clear. If we were to focus on the revenue line, we may wonder why there is such a large amount of volatility in the first half of the year.

However, the most notable feature in the moving average line is the steep decline towards the end of the year. This is probably a more serious issue.

Note that we could easily change the length of this moving average.

Let's press Alt + B + F, select manage measures, select moving average revenue, and edit.

The length of the moving average is defined by the final two arguments of the dates in period function.

We can change the type of interval from month to year, quarter or day and adjust the number of intervals.

Let's change the average from three months to two months, select OK and close.

The pivot table and pivot chart have both updated, although the difference is not particularly large.

The length of your moving average should be determined based on the business context and the data you have.

In this case, we only have 12 months of data, so it doesn't make much sense to use a period longer than three months.

Clearly three, or even two months, is enough to smooth out the volatility of the revenue line.

As you can see, moving averages can offer new insights on existing data. However, when creating moving averages, it's important that you understand the period that you're calculating your average over, as this can have a significant impact on the results.

In the next lessons, we'll continue to focus on time intelligence applications of DAX and look at how to make comparisons over time.

Excel Excel for Business Analytics Learning Plan
Practical Applications of DAX

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial