v7.2

DATE_OFFSET

DATE_OFFSET("TimeLevelValue", "TimeLevel", Offset)

DATE_OFFSET works only in dynamic project variables, not in node formulas.

The DATE_OFFSET function offsets a time level value forward or backward by a number of years, quarters, months, weeks, or days.

Use this function in dynamic project variables when one time level value should always be derived from another rather than entered manually (for example, the forecast end from the forecast start): DATE_OFFSET("$FORECAST_START", "Month", 12).

Parameters

TimeLevelValueLevel valueRequired: A value of any time level in the project’s time dimension, given as a quoted string or as a variable reference (for example "2026-01" or "$FORECAST_START").
TimeLevelLevel nameRequired: The time level on which to offset. Allowed values: "Year", "Quarter", "Month", "Week", "Day".
OffsetIntegerRequired: The number of TimeLevel steps to offset by.

Output shape

Return type: Level value
Format: Same format as the input TimeLevelValue

Watch out

Both TimeLevelValue and the resulting value must be valid level values within the project’s time horizon.
Offset can be negative: positive values offset forward in time, negative values offset backward.
When the level on which you offset is smaller than your input’s level, the offset starts from the first day of your input. The result is always returned at your input’s level. See the “Offset a quarter by months” example below.
Only time-dimension levels are accepted. Other dimensions cannot be passed here.

Examples

Add one year

Formula: DATE_OFFSET("2026", "Year", 1)

TimeLevelValue	TimeLevel	Offset	DATE_OFFSET Result
`"2026"`	`"Year"`	`1`	`"2027"`

Subtract one day

Formula: DATE_OFFSET("20260101", "Day", -1)

TimeLevelValue	TimeLevel	Offset	DATE_OFFSET Result
`"20260101"`	`"Day"`	`-1`	`"20251231"`

Derive a forecast end from a forecast start

If a project has a fixed variable $FORECAST_START = "2026-04", you can define a derived variable FORECAST_END as:

Formula: DATE_OFFSET("$FORECAST_START", "Month", 12)

TimeLevelValue	DATE_OFFSET Result
`"2026-04"`	`"2027-04"`

The derived variable updates automatically whenever $FORECAST_START is changed.

Derive last actuals from forecast start

A common pattern: the last actuals month is one month before the forecast start.

Formula: DATE_OFFSET("$FORECAST_START", "Month", -1)

TimeLevelValue	DATE_OFFSET Result
`"2026-04"`	`"2026-03"`

Offset a quarter by months

"2026-Q3" is treated as July 1 (the first day of Q3). The offset moves from that date, and the result is given back as a quarter.

Formula	DATE_OFFSET Result	Why
`DATE_OFFSET("2026-Q3", "Month", 1)`	`"2026-Q3"`	July 1 + 1 month = August 1, which is in Q3
`DATE_OFFSET("2026-Q3", "Month", -1)`	`"2026-Q2"`	July 1 - 1 month = June 1, which is in Q2
`DATE_OFFSET("2026-Q3", "Year", -1)`	`"2025-Q3"`	July 1 - 1 year = July 1, 2025, which is in Q3 of 2025

Derive the planning year from the forecast start

To compute the year after the forecast start’s year, use DATE_EXTRACT to drop down to the Year level first, then offset by one year.

If $FORECAST_START = "2026-04":

Formula: DATE_OFFSET(DATE_EXTRACT("$FORECAST_START", "Year"), "Year", 1)

Step	Result
`DATE_EXTRACT("$FORECAST_START", "Year")`	`"2026"`
`DATE_OFFSET("2026", "Year", 1)`	`"2027"`

The combined result is "2027".