Concept: Relative Time in Formulas

To reduce the time needed to maintain formulas as time passes, you can create formulas that refer to relevant time. Relevant time changes based on the timespan of the cell. Absolute time remains the same, no matter where you are in the timespan.

To refer to relevant time, use this
as an expression of time: [time=this]. You can also use versionmonth
modified by this
: versionmonth(this).

Absolute versus Relative Time

Absolute Time	Relevant Time
Acct.Expenses[time=12/2018] For any cell in the timespan, the formula always grabs the same value from December 2018.	Acct.Expense[time=this-1] For all cells in the timespan, the formula always grabs the value of the previous time period. For the Mar 2018, it grabs Feb 2018. For Aug 2023, it grabs Jul 2023.
Acct.Expense[time=2018] For all cells in the timespan, the formula always grabs the rollup value of the year, 2018.	Acct.Expense[time=this.year] For all cells in the timespan, the formula always grabs the value of the rollup in the same period. For the Mar 2018, it grabs the rollup of 2018. For Aug 2023, it grabs the rollup of 2023.

Example of Dynamic Time in Headcount Plans

You track personnel data on a modeled sheet. The modeled sheet has an account: New Hires. To calculate the number of new hires for each month, you can use a formula like this to the New Hire account:

If(versionmonth(this) = versionmonth(ROW.StartDate),1,0).

This formula says: Populate New Hire with 1 if the start date in this row has the same version month as the current cell. If it's not the same, calculate zero.

Here are the modeled sheet rows:

Employee Name	Start Date
Jane Doe	Jan 2023
John Smith	Jul 2022
Mary Joe	Feb 2023
Jack Brown	Jun 2022

For the version month Jan 2023, the new hire account has a value of 1 because Jane was the only new hire in Jan 2023.