Moving average in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand the sales trend over time by smoothing out short-term fluctuations using a moving average.

📊 Data: You have monthly sales data for the past year, including the month and total sales amount.

🎯 Deliverable: Create a line chart in Tableau showing monthly sales and a 3-month moving average line to visualize the trend clearly.

Sample Data

Month	Sales
2023-01	1200
2023-02	1350
2023-03	1500
2023-04	1600
2023-05	1700
2023-06	1650
2023-07	1800
2023-08	1900
2023-09	2000
2023-10	2100
2023-11	2200
2023-12	2300

1

Step 1: Connect your sales data to Tableau and load the data source.

No formula needed; just connect the data file or database.

Expected Result

Sales data with Month and Sales columns is available in Tableau.

2

Step 2: Create a line chart showing monthly sales.

Drag 'Month' to Columns shelf and 'Sales' to Rows shelf. Change 'Month' to continuous date if needed.

Expected Result

A line chart showing sales values for each month from January to December 2023.

3

Step 3: Create a calculated field for the 3-month moving average of sales.

Create calculated field named '3-Month Moving Average' with formula: WINDOW_AVG(SUM([Sales]), -2, 0)

Expected Result

Calculated field that computes the average sales of the current month and the previous two months.

4

Step 4: Add the moving average line to the chart.

Drag '3-Month Moving Average' to Rows shelf next to 'Sales'. Change the mark type to Line for both measures.

Expected Result

The chart now shows two lines: the original monthly sales and the smoothed 3-month moving average.

5

Step 5: Format the chart for clarity and add a legend.

Use different colors for sales and moving average lines. Add axis titles and chart title 'Monthly Sales and 3-Month Moving Average'.

Expected Result

A clear, easy-to-read line chart with legend and titles showing sales trends and smoothed trend.

Final Result

Month  | Sales  | 3-Month Moving Average
-----------------------------------------
Jan    | 1200   | 
Feb    | 1350   | 
Mar    | 1500   | 1350
Apr    | 1600   | 1483
May    | 1700   | 1600
Jun    | 1650   | 1650
Jul    | 1800   | 1716
Aug    | 1900   | 1783
Sep    | 2000   | 1900
Oct    | 2100   | 2000
Nov    | 2200   | 2100
Dec    | 2300   | 2200

(Line chart with two lines: monthly sales and 3-month moving average smoothing the trend)

✓The 3-month moving average smooths out short-term ups and downs in sales.

✓Sales show a steady upward trend throughout the year.

✓The moving average line lags slightly behind actual sales but highlights the overall growth clearly.

Bonus Challenge

Create a 6-month moving average line and compare it with the 3-month moving average on the same chart.

Show Hint

Use WINDOW_AVG(SUM([Sales]), -5, 0) for the 6-month moving average calculated field.