Here’s a challenge we face all the time in Telecoms: we need a list of the most recent months of data in a table. For example, a Telecoms Dashboard, which might show data for each of the most recent 12 months.
For example, your Dashboard for May 2018 might show data from May-18, April-18, March-18, etc., all the way back to June of 2017. Those are the most recent 12 months.
A typical Telecoms Dashboard shows one or more Key Performance Indicators (KPI) by month for each of the last 12 months.
Using the most recent 12 months is quite common in many industries. So common, in fact, that is has a special name: Trailing Twelve Months, often abbreviated TTM. Ideally, the Trailing Twelve Months (TTM) are re-calculated as soon as you add data. That is, if you automate it.
In this article, I’ll show you how to set that up. Like so many things in Excel, it’s surprisingly simple. It’s more work to explain it than to actually do it. But it’s worth figuring it out. It’s a big part of automating your Dashboards.
Follow Along
You can download my workbook to follow along with this article. The workbook will help you understand what I’ve written, and you can copy/paste the cells into your own workbooks. I do ask for your email address so that I can send updates. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe any time.
The sample workbook already has data for several months. Whenever data is added to the dashboard, I want the TTM to update automatically. I also want to conform to typical Excel best practices so that the workbook won’t be fragile and it won’t confuse others who try to use it.
Approach
My approach to solving this problem is to first find the latest date in the raw data, then use that date to derive each of the 12 monthly dates.
The approach uses the MAX() function to find the latest date. To create the sequential months the DATE() function is ideal. As each earlier month is 1 month less than the previous month, I’ll use the ROWS() function to create a sequence of increasing (or increasing) numbers.
Let’s start with the MAX() function. Here is the syntax:
=MAX(range)
Where range is the range of cells where the raw data is input.
In the sample workbook, the raw data is in the range B1:C20. Including the first row in the range argument to MAX() is optional. I like to include column header row to make the meaning of the range clearer.
So now the completed formula looks like this:
=MAX(B1:C20)
And straightaway you can see it returns 7 May, 2018. When you do this is your own workbooks, you might need to assign a date number format. For a simple value like this, I like naming the cell so that functions which use this cell as an input are easier to read and understand. So, I’ll name this range Latest_Date by typing that in the Name Box to the left of the Formula Bar.
So, that completes the first part of automating TTM. The most recent date in our input data is identified automatically. The next part is to derive the latest 12 months.
Which Day of the Month
Here is a point to notice about the latest date returned by MAX(): for my Dashboard, I’m specifically interested in the year and month. Does it matter whether the day is the 7th or the 8th or the 19th? Not really, because I’m only interested in the year and the month.
But the day might be important, even in a monthly Dashboard. For example, if the date is used as a lookup or for a Pivot table, then the date matters. In that case, the day of the month you usually want to use is the first day. So, for this reason, as a general rule, I force my TTM dates to use the first day of each month. I do this by replacing the day argument of the DATE() function with the number 1, for the first day of the month.
There can be exceptions, I suppose. But the raw data in the input table still retains the actual date that was entered, so nothing is lost by assining my TTM dates to the first day of each month.
Excel Best Practice – Avoid “Magic Numbers” in Formulas
Before I continue I want to say that naked numbers should not be embedded directly into formulas. But embedding a naked number in the formulas is exactly what I just suggested by using a “1” for the day argument to DATE(). What’s going on?
Naked numbers are sometimes called “magic numbers” because they might have some special, magical property. When magic numbers appear inside a formula you can’t know the reason for them because there is no way to add a note or comment inside a formula to explain your intent. Readers of your workbook might be confused.
A good example is trying to embed the value of “Pi” (𝜋), 3.141592. 𝜋 is certainly a “magic number”. But not everyone has memorized its first few digits and so might not recognize 3.141592 as being 𝜋. In this case, Excel best practice would be to use the Excel function PI().
Another reason why embedding magic numbers in formulas is a bad practice is that Excel’s FILL HANDLE can’t be used to easily extend a formula. That’s true for us, as I show later in this article. If I use magic number then the FILL HANDLE won’t work to create all of the Trailing Twelve Months. And entering the numbers by hand is too much work!
In my workbook, I created a named range called Day_Of_Month, entered the value “1”, and used this named range in my formula instead of entering a naked “1” in the formula.
FILL HANDLEs
I’ve used the term FILL HANDLE without explaining it. Are you familiar with FILL HANDLE? The FILL HANDLE is the little “knot” in the lower right-hand corner of any selected cell. Here is an image of a FILL HANDLE. The FILL HANDLE is an easy way to quickly extend a formula or a format.
To use the FILL HANDLE, hover your mouse over the knot, then left-click and drag to copy the formatting and also fill the series at the same time. Right-click and drag will show a popup menu to choose what the Fill HANDLE should do.
Create the Sequential Dates
Not to be too clever about it, the way to get the last 12 months of dates, the TTM dates, is to simply subtract an integer from the month number of Latest_Date. Here’s how to do that.
I use the DATE() function to feed in the year, month and day components of Latest_Date and return the TTM date. I wrote above about using the named range Day_Of_Month for the day argument.
=DATE(YEAR(Latest_Date), MONTH(Latest_Date), Day_Of_Month)
The first TTM date can simply be Latest_Date. Since we will subtract from the month to get the older dates we also could say the latest TTM date is the month of Latest_Date minus 0. Then, the first older TTM date is the month of Latest_Date minus 1. The second older TTM date is the month of Latest_Date minus 2. And so on.
I wrote it that way to illustrate that we can simply subtract an integer from the month argument to get the next older month.
But what happens when the month is January, and we subtract 1 from that? Both the month and the year should change. It turns out the DATE() function is smart enough to know what we wanted, and to correct the year, 2017, while also displaying the correct month, 12 (December). That’s pretty cool, and it also saves us a bunch of work.
So, we could simply embed an integer into each formula. I’ve done that in the workbook, see the TTM dates next to text box “A”.
But I’ve already written that embedding numbers directly in formulas is a bad practice for Excel.
A Better Solution
A better solution would be to move the numbers out of the formula and into an adjacent column where the sequence is clearly visible. Then you’d add a link in the formula to the adjacent cell. That would avoid embedding a number in a formula, and also help to reveal the purpose.
You could even add a short note describing the purpose of the column. That complies with best practice, and it allows the FILL HANDLE to work for us. See text box “B” in the workbook for this solution.
The Best Solution
But in my opinion, the best solution is to NOT put the integers in an adjacent column. To create an increasing integer, use the ROWS() function directly in the formula. Here is how you do this.
ROWS() returns the count of the rows included by the range argument. If you anchor the first cell in your range and then use the FILL HANDLE to extend the formula, the value returned by ROWS() increases by 1 with each additional row in the range. Here is the formula to create a count which starts at 0 in the first row, and increases by one for every row in the range:
=(ROWS(G$5:G5)-1)
Now, just like with case A and case B above, this must be subtracted from the month argument to get the correct TTM date. The meaning of the “1” embedded in the formula should be clear enough that best practice is not violated. Here is the formula for the entire month argument:
=MONTH(Latest_Date)-(ROWS(G$5:G5)-1
Altogether, that returns the month number for the current month. This is the argument passed to the DATE() function.
So here is the complete formula to enter in the first cell of the TTM range:
=DATE(YEAR(Latest_Date),MONTH(Latest_Date)-(ROWS(G$5:G5)-1),Day_Of_Month)
Now use the FILL HANDLE to extend the formula to each cell in the TTM range.
This approach mostly avoids embedding a number in the formula and also avoids cluttering your workbook with an unnecessary column. That’s exactly what we want!
Latest First, or Oldest First?
Note that this approach put the most recent year-month in the top row of the range. If you want to reverse the order, to show the oldest TTM date in the first row and the latest TTM date in the last row, anchor the range argument in the last row of the TTM dates, like this:
=ROWS(G$16:G16)-1)
where the first part of the range argument is anchored, and the second part is not anchored. Now when you use the FILL HANDLE, fill up to populate the entire range.
Conclusion
Here is a brief summary to this lengthy article. Use MAX() to find the latest date in your raw data. Then, use DATE() to create the 12 months, by subtracting an increasing integer from the month argument. Easy!
Once again, here is the link to download the workbook.
If you like these Excel Telecom Tricks, and my other Telecoms technology and career-building tips, connect with me on LinkedIn Russell Lundberg or follow me on Twitter TelecomVoices