When we worked on some table calculations today, I stumbled into the problem of thinking 'why do I only need to state '12' as how many previous values to include when I want to look at a '13' week moving average? A lot of Table Calculation configurations confused me, so I wanted to make a quick blog just to clear it up for anyone who got confused like me.

Case Study

Using superstore, we'd like to see how the sum of sales grows on a weekly scale, from 2017-2020. To get this view, we drag Sales to rows, and Order Date to columns. Right-click and change the order date from the column shelf to be showing continuous months.

As we can see, since we are looking at a weekly level, there are some quite regular fluctuations between values, causing the graph to be difficult to read. We may solve this by bringing the view up to a monthly level, but we may also want to look at a moving average to smooth the points, showing how the trend is developing over time.

To achieve this, we need to add a table calculation. Since we don't have multiple dimensions in the view, we can use a quick table calculation to achieve the correct tabulation format we want (table across).

So, right click your sales from the rows shelf, add a quick table calculation, and select 'moving average'. This should default to this view:

If we open up our 'edit table calculation' we should see the following:

However we see 'prev 2' in the configurations. This is the point I got quite confused, before expanding this field and looking at the additional configurations.

As we can see, we are using the two previous values, but 'Current value' is also ticked. When this is ticked, it means that the current value for that week is also included in the calculation. For example, if we are calculating our moving average for 23/11/2020, we will be using values from 09/11/2020 and 16/11/2020, and the current value (23/11/2020).

Carrying on this logic, if we wanted to calculate a 13 week moving average, we simply replace 'previous values' with 12. You should see the following if you configure it like I have:

If we wanted to use future values too, we would set previous values to 6, next values to 6, and this would achieve a moving average looking at the previous 6 weeks, the current value, and the next 6 weeks when calculating the value.

I hope this helps anyone who struggled like I did to wrap my head around this!