In this post, I’m sharing a method to create various types of TTM reports including visualization using Excel. While there are various methods I have discovered, I believe my method presented here is fool-proof and also intuitive. It uses EOMONTH function for error-free calculation of each month’s end, and FILTER function to filter and fetch specific data from a given criteria making it very robust and flexible.
As a reminder, Trailing 12 Months, aka TTM, refers to a time period covering the past 12 consecutive months of data starting from a given date. TTM is a rolling measurement, so the data updates every month. One of its most common uses of a TTM report is to show revenue representing the total revenue a company has earned over the last 12 months. The powerful and the challenging feature of implementing this is the fact that the starting date can be virtually any date (as long as we have the data for that time period of course), and the formulas should be able to fetch the correct data starting from that starting date, and previous 11 months, making a total of 12 months. Let’s demonstrate this with some sample data.
I have the following revenue and profit information of a sample company spanning Jan 2020 to Dec 2025. The sample dataset is shown below.
Objective
We want to be able to select any starting date, and get a report of that month and previous 11 months’ revenues and/or profit information. Additionally, we also want a simple chart to visualize revenue and profit in that time period. Column A contains the Date, B contains the Revenue, and C contains the Profit values.
The Method
For the starting date input, I set up a cell E3 for the date intput. To show the month-by-month revenues for the TTM period, I have a formula that reads the input from E3 then it goes to work. There are various ways to achieve this including really long, nested SUMIFS and such, but I believe the most elegant formula to use is FILTER in combination with EOMONTH. My beautiful formula is: =FILTER(range, constraint)
It creates a constraint on time periods based on E3 cell value by using an AND condition for the filter function. It uses EOMONTH function to ensure that the end of months (taking into account various nuances incuding the situations when different months have different maximum days, and different years may or may not be a leap year).
If the input date is out of range of the dataset’s minimum and maximum dates, it’ll generate a #CALC! error. To refine the formula further, I nest it with IFERROR to ensure we catch those situations and return an informative message instead of an error. So, the formula is refined as: =IFERROR(FILTER(range, constraint), “Invalid starting date.”)
Take a look at the video clip below to see how the all the output tables and charts update accurately and instantly when a new date is entered in the input cell (orange).
Video clip of interaction and dynamic report updates
Similarly using the same formula concept, I fetch the Profit numbers as well as the Revenues in table Rep 2 (see video above). The only change is to include column C (containing profit) in the filter range.
In the table Rep 3, I show only the dates and profit and explicitly skip the in-between column B containing Revenue. In order to do that, I create two distinct arrays and use CHOOSE function where I specify the arrays’ indices to fetch. This is done with this beautiful formula: =IFERROR(FILTER(CHOOSE({1,2}, array1, array2), constraint), “Invalid starting date.”)
And of course we want the sums…of both revenues and profits for the TTM. For that, I use the same FILTER concept as above with the same constraints, and only wrap it in SUM function. That’s the cleanest way to accomplish this without having to use multiple SUMIF or SUMIFS. The formulas are simply: =SUM(FILTER(revenue_range, constraint)) for Sum of Revenue TTM, and =SUM(FILTER(profit_range, constraint)) for Sum of Profit TTM.
Finally, I create a stacked column chart based on the returned FILTERed datasets. The subtitle of the chart also clearly indicates the time range for the TTM that it’s showing. That subtitle is also dynamically generated by a formula based on the starting date input.
If you are able to find a more elegant formula to achieve this, I’d be very interested to see the solution as I haven’t found any other that’s this simple, intuitive and also robust (meaning, the method won’t break when the dates are sorted, or have missing rows, etc.). I hope you found this post helpful and it saves you time in creating your beautiful reports!
Better yet, get the full workbook solution from here! This is a complete workbook solution including visualization using Excel using elegant, fool-proof method to generate trailing-twelve-month report. It includes sample dataset and solution for TTM Revenues and TTM Profit—everything you need for generating the report. This is fully customizable with your own data and additional visualizations and reports as needed.
