Moving average in Tableau - Cell-by-Cell Formula Trace

Sample Data

Daily sales data for five days.

Cell	Value
A1	Date
B1	Sales
A2	2024-01-01
B2	100
A3	2024-01-02
B3	120
A4	2024-01-03
B4	130
A5	2024-01-04
B5	90
A6	2024-01-05
B6	110

Formula Trace

WINDOW_AVG(SUM([Sales]), -2, 0)

Step 1: SUM([Sales]) at 2024-01-01

Step 2: SUM([Sales]) at 2024-01-02

Step 3: SUM([Sales]) at 2024-01-03

Step 4: Calculate WINDOW_AVG for 2024-01-03 using sales from 2024-01-01 to 2024-01-03

Step 5: Calculate WINDOW_AVG for 2024-01-04 using sales from 2024-01-02 to 2024-01-04

Step 6: Calculate WINDOW_AVG for 2024-01-05 using sales from 2024-01-03 to 2024-01-05

Cell Reference Map

    A          B
1 | Date     | Sales |
2 | 2024-01-01 | 100  |
3 | 2024-01-02 | 120  |
4 | 2024-01-03 | 130  |
5 | 2024-01-04 | 90   |
6 | 2024-01-05 | 110  |

Arrows show WINDOW_AVG uses current row and two previous rows in column B (Sales).

The formula references the Sales values in column B for the current date and the two previous dates to calculate the moving average.

Result

    A          B       C
1 | Date     | Sales | Moving Avg |
2 | 2024-01-01 | 100  |           |
3 | 2024-01-02 | 120  |           |
4 | 2024-01-03 | 130  | 116.67    |
5 | 2024-01-04 | 90   | 113.33    |
6 | 2024-01-05 | 110  | 110.00    |

The Moving Avg column shows the 3-day moving average of sales, starting from the third date because the first two dates don't have two previous days to average.

Sheet Trace Quiz - 3 Questions

What sales values are used to calculate the moving average on 2024-01-04?

A Sales on 2024-01-02, 2024-01-03, and 2024-01-04

B Sales on 2024-01-01, 2024-01-02, and 2024-01-03

C Sales on 2024-01-03, 2024-01-04, and 2024-01-05

D Only sales on 2024-01-04

Key Result

WINDOW_AVG(SUM([Measure]), -N, 0) calculates the average of the current and previous N rows.