Plan supports time shifting and lookup functions that retrieve values from different time periods. These functions help you shift between time periods and access measure values for comparative and trend analysis.
SHIFT
The SHIFT function returns a date by shifting a specified date forward or backward by a given time interval, such as months, quarters, or years.
Syntax
SHIFT(inputDate, offset)
Arguments
inputDate: The date to shift.
offset: Specifies the number of time intervals by which the date is shifted. Use a positive value to shift the date forward and a negative value to shift it backward. Supported interval formats include months (M), quarters (Q), and years (Y).
Return value
Returns the date obtained by shifting the input date by the specified time interval.
Examples
SHIFT(COLUMN.CURRENT_PERIOD, "2M")
Returns the date two months after the current period. For example, if the current period is January, the function returns the corresponding date in March.
Note
Set the Data type to Date when configuring the SHIFT function to view the shifted date. Otherwise, the function returns a #VALUE! error.
The following formula shifts each Open Date by two months. For example, if the Open Date is 05/22/2026, the function returns 7/22/2026.
You can also use the SHIFT function with forecast open and closed periods. For example, you can shift the forecast open period end date backward by two quarters.
VALUEAT
The VALUEAT function returns the value of a measure at a specified time offset relative to the current reporting period.
Syntax
Arguments
measure: The measure for which the value is returned.
offset: Specifies the number of time intervals by which the value is shifted. Use a positive value to retrieve a value from a future period and a negative value to retrieve a value from a previous period. Supported interval formats include months (M), quarters (Q), and years (Y).
Return value
Returns the value of the specified measure at the given time offset.
