Create a Rolling 12-Month Report

Build a report whose time columns automatically advance each month without manual updates.
Tags:

A rolling 12-month report always shows the current month plus the 11 preceding months — no matter when you open it. This tutorial shows how to build one using OfficeConnect’s relative time contexts so the columns update automatically on each refresh.

What you’ll need:

  • An existing OfficeConnect workbook or a blank one (Build Your First Report)
  • Your Adaptive Planning instance populated with at least 12 months of actuals or forecast data

How rolling time works in OfficeConnect

OfficeConnect supports relative time contexts — time elements that are defined as offsets from the current period rather than as fixed calendar months. When you use Current Month, Current Month -1, Current Month -2, etc., OfficeConnect resolves each offset at refresh time against the current date.

This means your column headers and data update automatically every month without you touching the workbook.

Step 1 — Set up the column headers

1
Select the header row Click cell B1 in your workbook. This will hold your oldest month (Current Month -11).
2
Add a relative time element In the Reporting pane, expand Time → Relative Periods. Drag Current Month -11 into B1. OfficeConnect inserts a time context formula.
3
Fill the remaining 11 columns Repeat for C1 through M1, adding Current Month -10 through Current Month in order. You should now have 12 header cells spanning a rolling year.

Step 2 — Add account rows

4
Add your accounts Click cell B2. In the Reporting pane, expand Accounts and drag your first account (for example, Revenue) into B2. Drag your version into A2.
5
Extend across the row OfficeConnect formulas reference the time element in the header row, so you can copy cell B2 across to M2 and each cell will pick up the correct month automatically.
6
Add more account rows Copy row 2 down for each account you need (Gross Profit, Operating Expenses, Net Income, etc.). The time headers are shared, so the copies resolve correctly.

For repeating rows across many accounts, see Repeating Reports.

Step 3 — Refresh and verify

7
Click Refresh Click Refresh on the OfficeConnect ribbon. All 12 columns should populate with data for the rolling window ending on the current month.
8
Verify the month labels Confirm that B1 shows the month 11 periods ago and M1 shows the current month. If the labels look wrong, check that your Adaptive Planning instance has the Current Period set correctly in Administration settings.

Step 4 — Lock the column widths and save

9
Format the header row OfficeConnect time context labels can be long. Shorten them by adding a custom Excel number format to the header cells (for example, mmm-yy) so they display as Jan-25, Feb-25, etc.
10
Save and schedule a refresh Save the workbook. Each month, open the file and click Refresh — the window shifts forward automatically. No column editing needed.

Next steps