Coming Soon! We’re launching a new sub-community within the Google Cloud Community dedicated to cloud security: The Google Cloud Security Community. In preparation for the launch, this site will be in read only mode from 22 September 12am PST - 23 September 7pm PST

How to Calculate 12-Month Trailing Average (TTA) in a Look

I’ve figured out how to calculate a 12-month trailing average (TTA) in a look for the purpose of understanding trends.  Here’s how I did it.  It requires the use of a custom filter to select the right data and then a table calculation to get the result.

Step 1 - Custom Filter

In your new Look, create a Custom Filter to select the 12 months previous to the last completed month.  Using a date-month field, here’s the formula:

MONTH-DATE-FIELD-HERE<trunc_months(add_months(-1,now()))AND MONTH-DATE-FIELD-HERE>trunc_months(add_months(-14,now()))

Depending on the field you’re using, you may need to use -13 instead of -14 to get 12 months.  Validate that by looking at your data by month.

Step 2 - Add a table calculation

Using the field you’re measuring, here’s the formula:


You can format the field so you don’t get long decimal points in your result.


On your other measure, select “Hide in Visualization” and change the Visualization to Single Value to display the TTA.

I hope this helps.  Please comment if you have further suggestions.

3 0 800
Top Labels in this Space