Tableau: LTM Moving Calculation (Finance KPI)

3 min readNov 19, 2016

In this post the simple useful version of LTM calculation will be shown, done in Tableau Software (based on the Sample Superstore data).

By the definition, LTM (last twelve months) is the time frame of the preceding 12 months in reference to a financial metric used to evaluate a company’s performance. Although a 12-month period is a relatively short time span for examining a given equity evaluation measure, it is considered useful because of the fact that it indicates a company’s most recent performance, and therefore may be indicative of the company’s current trend.

But it is also a long enough to level out seasonal factors, possible short-term price fluctuations and market swings.

Let’s start with the real example.

Open the new workbook, connect to EU Superstore data source, choose measure Profit and dimension Order Date:

In order to make LTM calculation, duplicate SUM(Profit) in the Rows, click on drop-down list, choose Add Table Calculation. In Calculation Type choose Moving Calculation — including sum with previous 12 values, next 0 values and without current value:

What have we done ? What we want and what we have done is the last 12 months sum calcution excluding current month. That means that e.g. in Apr 2015 we will see sum of all values from Apr 2014 to Mar 2015; in May 2015 sum of all values from May 2014 to Apr 2015, …

LTM calculation is made and we can stop here :) But what I want to show further is some simple useful modification.

Lets say that we want to show values just for years 2014 and 2015. In oder to do this, the first idea would be to put Order Date in filter and choose those two years.

If we do that, we will get different (wrong) second graph!

Especially give the attention to the values in 2014 of LTM calculation ! What happened ?

Since we defined the calculation as a sum of previous 12 months, and filtering put out older data, the calculation can be done just with available data. It means that e.g. in Apr 2014 we will have only the sum of 3 months values: from Jan 2014 to Mar 2014.

To make correctly our first attention, put out the given filter in order to have again all data, right click on 2012 and 2013 column headers and choose Hide: