OFFSET returns a value from a period before or after the current period. 

You could use OFFSET to compare how today's regional sales compare to yesterday's. 

OFFSET(Value to offset, Offset amount, Substitute value)

ArgumentData TypeDescription
Value to offsetNumber, Boolean, date,
time period, list, or text
Value to replace with a value from a different time period.
Offset amountNumber

Number of periods forward from which to retrieve a value.

Positive values refer to future periods, negative to past periods, and zero the current period.

Substitute valueSame as Value to offset
Value to apply if the Offset amount specifies a period outside of the model's time range.

The OFFSET function returns a value of the same data type as the Value to offset argument.

This function allows for three arguments. If you have these functions with two arguments and don't declare a third one, this will default to Time. If you declare a third argument, then you can use any related dimension to the line item as an argument.

In Polaris, a value of NaN (Not a Number) for the Offset amount argument returns the Substitute value argument.

In the Classic Engine:

  • A value of NaN is equivalent to 0.
  • You can use the OFFSET function only with a time dimension. In Polaris, you can use OFFSET with any dimension except Versions.
  • If the Expression argument has a data type of list or time period, and does not resolve to a different data type, the result line item must have the same data type.

OFFSET(Value to offset, 2, 0)


JanFebMarAprilMayJune
Value to
offset

  3,000

4,000

4,500

 3,700

3,500

4,100
OFFSET
(Value to offset, 2, 0)
  4,5003,700 3,500 4,100       0      0

OFFSET is the same as LEAD in the NONSTRICT mode.

The Value to offset argument and result line item must share the same time range. Any formula that conflicts with this rule is rejected. For more information see the Time and Date Functions page.


JanFebMarAprilMayJune
Value to
offset

 3,000

 4,000

4,500

 3,700

3,500

4,100
Substitute
value

2,800

3,800

3,200

3,500

3,300

3,900
OFFSET(Value to offset, -1,
Substitute value)
 2,8003,0004,0004,5003,7003,500