Create a Trailing 12-Month Report
How to build a report that always shows the most recent 12 months of data.
A trailing 12-month (T12M) report shows the 12 most recent calendar months and advances automatically each month. This is a common layout for rolling actuals analysis.
How to build it
The key is using relative time elements for each of the 12 columns — each column is defined as “N months ago” relative to today.
1
Set up your rows with account elements
Apply your account elements (e.g., Revenue, COGS, Gross Profit) to rows as usual.
2
For the first column, apply 'Current Month - 11'
In the Elements tab, expand Time → Relative. Find the relative time element for 11 months prior (the oldest month in your T12M window) and apply it to column C.
3
For each subsequent column, apply the next relative month
Continue applying relative months across columns:
- Column C:
Current Month - 11 - Column D:
Current Month - 10 - Column E:
Current Month - 9 - …
- Column N:
Current Month(most recent)
4
Refresh
The report shows 12 months of data. Each month you refresh, the window advances by one month automatically.
Add a YTD column
After the 12 monthly columns, add a YTD column:
5
Select the column after your last month
Click the column letter immediately to the right of your final monthly column.
6
Apply Current Month as the time element
Drag
Current Month from the Reporting pane into the header cell of this column.7
Apply YTD as a context element
In the same column, drag
YTD as a context element alongside the time element.8
Refresh
Click Refresh — this column shows the year-to-date sum as of the current month.
Formatting tip
Use Excel’s column headers to show friendly month names using an OfficeConnect label:
1
Select the header cell above a monthly column
Click the header cell (the cell directly above a monthly data column).
2
Click Labels in the OfficeConnect ribbon
In the OfficeConnect ribbon, click Labels.
3
Set the Label Type and format
Select Time as the Label Type and the appropriate format for the Label Type Value.
4
Refresh
Click Refresh — the header automatically shows the correct month name.