Today, I want to show you how to create this dashboard featuring three charts for averages. If you want to follow along, I have added a link below to the file on Tableau Public. I am going to take the first chart on the left, Average, and duplicate it to create the other two views.

The Challenge: The Moving Average Dilemma

You will notice that the chart on the right is filtered. The chart in the middle, Moving Average, contains dates from 2020 down to 2023. What I really want is the chart on the right: the Filtered Moving Average.

However, to calculate a moving average for the beginning of 2023, I still need the previous year's data (2022) to be available to the calculation. If I use a standard Dimension Filter, Tableau will strip that 2022 data away before the calculation runs, resulting in incorrect values. The solution is to filter The View, but not the Underlying Data. We achieve this by using a Table Calculation—specifically the $LOOKUP$ function—on the Filters shelf.

Step 1: Create the Moving Average

To begin, I’ll take the Average sheet and duplicate it. I’ll rename the sheet and the title to Moving Average. On the Marks card, I click the drop-down on the measure and change it to the Quick Table Calculation called Moving Average. You’ll notice a small delta (triangle) symbol on the pill, which indicates this is now a Table Calculation. I can edit it to verify it is indeed a moving calculation.

Step 2: Create the Date Lookup Filter

I’ll duplicate this chart once more and name it Filtered Moving Average. Now, I need to create the specific Calculated Field that allows us to filter the view without losing the underlying data.

1. I’ll name this field Date Lookup Filter.

2. I’ll enter the following calculation: $LOOKUP(MIN([Date]), 0)$.

3. As I type, Tableau suggests the syntax. Once it says "The calculation is valid" in the bottom left, I click OK.

Step 3: Apply the View Filter

Now, I want to change this new field to Continuous (green pill). I need it to be continuous for the slider filter that follows. I’ll drag Date Lookup Filter over to the Filters shelf. I can now use the slider to zero in on just the 2023 dates.

Because $LOOKUP$ is a Table Calculation, it is executed after the moving average is calculated in Tableau’s Order of Operations. The view is filtered visually, but the 2022 data is still "behind the scenes" powering the moving average.

Step 4: Comparison on the Dashboard

I’ll add these to the dashboard so you can see them together. I’ll drag the Moving Average chart and the Filtered Moving Average chart into place (using the gray zones to guide the layout).

If you look at February 2023, the data point is 323 on the middle chart. In the filtered chart on the right—which hides 2022—the February value is still 323. This proves that even though 2022 isn't visible, it is still being used to calculate the correct moving average.