Return Nulls in Moving Average Calculation if there is Not Enough Data to Compute Average

KNOWLEDGE BASE

Return Nulls in Moving Average Calculation if there is Not Enough Data to Compute Average

Published: 18 Jan 2017
Last Modified Date: 18 Jan 2019

Question

How to show nulls, rather than a 'shortened average' for dates where there is less data than the X days required to complete a moving average?

Environment

Tableau Desktop

Answer

Create a new calculated field to replace the original moving average calculation using the formula below:

IF ISNULL(LOOKUP(SUM([Measure]),-X)) THEN NULL ELSE WINDOW_AVG(SUM([Measure]), -X, 0) END

"X" is the number of days that the moving average is computed from. This calculation allows the moving average to only be computed if there are at least X days worth of data, and will display a NULL if there aren't enough.