4 Replies Latest reply on May 24, 2016 1:39 AM by Stephen Lavery

# Trailing 12 Months / Trailing 36 Months (but with Calculations)

All,

I'm trying to solve this issue here of calculating something for the last 12 months as well as the last 36 months. However, I'm not trying to do something as simple as a Sum (if I were, I would just use the IF function)

For this calculation, I need Tableau to basically find all the values in the last 12 months, Average them, and then add 1.5. (For example, for the TTM calculation, it would average out the values from Row 4 to 11, and then add 1.5, which would then give a value of 3.81)

Edit: I also want to show TTL and Trailing 36 months on the same table, which is why I haven't looked into filtering the data

I've attached a document with dummy data that hopefully will help.

Thanks so much!

• ###### 1. Re: Trailing 12 Months / Trailing 36 Months (but with Calculations)

Hi John,

This works but the leap year kind of messes it up. Create a calculation called 'Days Since Today':

-1*(DATEDIFF('day',TODAY(),([Date])))

Then create this calculation ''Last 12 Months Value':

IF [Days Since Today]<=366 THEN [Value] END

Note: Every other year apart from leap years this calculation would be: IF [Days Since Today]<=365 THEN [Value] END

Finally create a calculation for the value you're after:

(AVG([Last 12 Months Value]))+ 1.5

This does return 3.81 but again I'm not sure how you're going to work around the leap year problem... See the attached workbook.

Kind Regards,

Stephen

• ###### 2. Re: Trailing 12 Months / Trailing 36 Months (but with Calculations)

Hi,

I created calculated fields for the count and the sum.

For trailing 36 months, you can change -12 to -36.

Please let me know if this helped.

Thank you.

-Mia-

1 of 1 people found this helpful
• ###### 3. Re: Trailing 12 Months / Trailing 36 Months (but with Calculations)

Stephen -- thanks so much for that, it worked like a charm!