Rolling period calculations in Tableau - Step-by-Step Guide

Introduction

Rolling period calculations help you see trends over time by averaging or summing values for a set number of past periods. This smooths out ups and downs and shows clearer patterns in your data.

When you want to show the average sales of the last 3 months on a monthly sales chart.

When you need to display the total revenue for the past 7 days on a daily dashboard.

When your manager asks for a 12-month moving average to understand yearly trends.

When you want to compare current week performance against the average of previous weeks.

When you want to smooth out daily website visits to see overall traffic trends.

Steps

Step 1: Open your Tableau workbook

- Tableau Desktop main window

Your data and worksheets are visible

Step 2: Create a calculated field

- Analysis menu > Create Calculated Field

A dialog box opens to enter the formula

Step 3: Type the rolling calculation formula

- Calculated Field dialog

Formula is ready to use in your view

💡 For a 3-month rolling sum, use: WINDOW_SUM(SUM([Sales]), -2, 0)

Step 4: Click OK to save the calculated field

- Calculated Field dialog

The new field appears in the Data pane

Step 5: Drag the new calculated field to Rows or Columns shelf

- Worksheet view

The chart updates showing rolling values

Step 6: Right-click the calculated field on the shelf and select Edit Table Calculation

- Shelf context menu

Table Calculation dialog opens for configuration

Step 7: Set the calculation direction and partitioning

- Table Calculation dialog

Rolling calculation correctly computes over desired time periods

💡 Choose 'Table (Across)' for time on columns or 'Table (Down)' for time on rows

Before vs After

Before

Line chart shows monthly sales with sharp ups and downs for 12 months

After

Line chart shows smoother sales trend with 3-month rolling sum applied, reducing sharp spikes

Settings Reference

Calculation Type

📍 Table Calculation dialog

Defines the type of rolling calculation to perform

Default: Window Sum

Compute Using

📍 Table Calculation dialog

Determines the direction and scope of the rolling calculation

Default: Table (Across)

Start and End Offset

📍 Table Calculation dialog

Sets how many periods before and after the current row to include

Default: Start: -2, End: 0 for 3-period rolling

Common Mistakes

Not setting the correct Compute Using direction

The rolling calculation sums or averages over wrong data points, giving incorrect results

Always check and set Compute Using to match your time dimension layout (Across or Down)

Using WINDOW_SUM without offsets

Calculates sum over entire table instead of rolling window

Specify start and end offsets like WINDOW_SUM(SUM([Sales]), -2, 0) for last 3 periods

Summary

Rolling period calculations show trends by averaging or summing over past time periods.

Use Tableau's WINDOW functions with proper offsets and Compute Using settings.

Always verify the calculation direction matches your time dimension layout.

Practice

1. What is the main purpose of a rolling period calculation in Tableau?

easy

A. To filter data based on a fixed date range

B. To calculate values over a moving window of past data points

C. To create a static total of all data points

D. To sort data alphabetically

Solution

1. Step 1: Understand rolling period concept

   Rolling period calculations summarize data over a moving window, like last 3 months.

2. Step 2: Identify purpose in Tableau

   Tableau uses rolling calculations to track recent trends dynamically, not fixed totals or sorting.

3. Final Answer:

   To calculate values over a moving window of past data points -> Option B

4. Quick Check:

   Rolling calculation = moving window summary [OK]

Hint: Rolling means moving window over recent data points [OK]

Common Mistakes:

• Confusing rolling with fixed totals
• Thinking rolling sorts data
• Assuming rolling filters data

2. Which Tableau function is used to calculate a rolling sum over a window of data?

easy

A. RUNNING_SUM()

B. SUM()

C. WINDOW_SUM()

D. TOTAL()

Solution

1. Step 1: Identify rolling sum function

   WINDOW_SUM() calculates sum over a specified window, perfect for rolling sums.

2. Step 2: Differentiate from similar functions

   SUM() totals all data, TOTAL() sums entire partition, RUNNING_SUM() accumulates from start to current row, not a fixed window.

3. Final Answer:

   WINDOW_SUM() -> Option C

4. Quick Check:

   Rolling sum = WINDOW_SUM() [OK]

Hint: Use WINDOW_SUM() for sums over moving windows [OK]

Common Mistakes:

• Using SUM() which sums all data
• Confusing RUNNING_SUM() with rolling window
• Using TOTAL() which sums entire partition

3. Given the Tableau calculation WINDOW_AVG(SUM([Sales]), -2, 0), what does it compute?

medium

A. Average of sales for the current and previous two rows

B. Average of sales for the next two rows only

C. Sum of sales for all rows

D. Average of sales for the current row only

Solution

1. Step 1: Analyze WINDOW_AVG parameters

   WINDOW_AVG computes average over a window; -2 to 0 means from two rows before to current row.

2. Step 2: Understand SUM inside WINDOW_AVG

   SUM([Sales]) aggregates sales per row, then WINDOW_AVG averages over the window of 3 rows.

3. Final Answer:

   Average of sales for the current and previous two rows -> Option A

4. Quick Check:

   Window from -2 to 0 = current + 2 previous rows [OK]

Hint: Negative start index means look back rows [OK]

Common Mistakes:

• Thinking window looks forward only
• Confusing sum and average
• Ignoring window range parameters

4. You wrote this Tableau calculation for a 3-month rolling sum: WINDOW_SUM(SUM([Profit]), 0, 2). The results seem incorrect. What is the likely issue?

medium

A. SUM() cannot be used inside WINDOW_SUM()

B. The calculation needs to use RUNNING_SUM() instead

C. WINDOW_SUM() requires negative indices only

D. The window range is forward-looking, not backward-looking

Solution

1. Step 1: Check window range meaning

   Range 0 to 2 means current row and next two rows, which looks forward, not backward.

2. Step 2: Understand rolling sum intent

   Rolling sums usually look backward (previous periods), so range should be negative to zero, e.g., -2 to 0.

3. Final Answer:

   The window range is forward-looking, not backward-looking -> Option D

4. Quick Check:

   Rolling sum needs backward window range [OK]

Hint: Rolling sums usually use negative start index [OK]

Common Mistakes:

• Using forward window range for rolling sums
• Thinking SUM() is invalid inside WINDOW_SUM()
• Confusing RUNNING_SUM() with rolling sum

5. You want to create a 6-month rolling average of sales in Tableau, but your data has missing months. Which approach ensures accurate rolling calculations?

hard

A. Use a continuous date axis and fill missing months with zero sales before applying WINDOW_AVG

B. Apply WINDOW_AVG directly on raw data without adjustments

C. Use RUNNING_SUM instead of WINDOW_AVG to ignore missing months

D. Filter out months with missing sales before calculation

Solution

1. Step 1: Understand impact of missing months

   Missing months cause gaps, so rolling averages skip those periods, giving inaccurate results.

2. Step 2: Fill missing months with zero sales

   Using a continuous date axis and filling missing months with zero ensures the rolling window covers all months evenly.

3. Step 3: Apply WINDOW_AVG on adjusted data

   Now WINDOW_AVG calculates correctly over 6 months including zeros for missing months.

4. Final Answer:

   Use a continuous date axis and fill missing months with zero sales before applying WINDOW_AVG -> Option A

5. Quick Check:

   Fill gaps first for accurate rolling average [OK]

Hint: Fill missing dates with zeros before rolling average [OK]

Common Mistakes:

• Ignoring missing months causing wrong averages
• Using RUNNING_SUM which accumulates, not averages
• Filtering out missing months, shrinking window size