1. Calculation functions
  2. All Functions
  3. Time and Date Functions
  4. LEAD

Use the LEAD function to return a value offset by a number of periods.

Syntax

LEAD(Value to offset, Offset amount, Substitute value [, Non-positive behavior])

ArgumentData typeDescription
Value to offset (required)Number, Boolean, date, time period, list, textThe value to replace with the value from a different time period.
Offset amount (required)NumberThe number of periods away to retrieve the value from. Positive values refer to future periods, and negative values to past periods. Zero refers to the current period.
Substitute value (required)Same as the Value to offset argumentThe value to use if the Offset amount argument specifies a period outside of the module’s time range. Also used for some non-positive values if you use the SEMISTRICT or STRICT keywords for the Non-positive behavior argument.
Non-positive behavior (optional)KeywordDetermines if the LEAD function should use the Substitute value instead if the Offset amount is negative or zero.

The keywords for the Non-positive behavior argument and their effects are under the Non-positive behavior heading below.

Returns

The LEAD function returns a result of the same format as the Value to offset argument.

Arguments

Value to offset

The Value to offset argument contains the values to replace with values from a relative time period. It can refer to a line item, property, or expression of any format.

Offset amount

The Offset amount argument is the number of periods to offset the Value to offset argument by. A positive Offset amount specifies a value from a future period, and a negative Offset amount specifies a value from a past period. If zero, Offset amount specifies the value from the same period.

The Offset amount argument can refer to a number-formatted line item, property, or expression.

Substitute value

The Substitute value argument provides the value for the LEAD function to return if:

  • the Offset amount argument specifies a period outside of the module’s time range
  • the SEMISTRICT keyword is used for the Non-positive behavior argument and the Offset amount argument is negative
  • the STRICT keyword is used for the Non-positive behavior argument and the Offset amount argument is negative or zero

The Substitute value argument must be a line item, property, or expression of the same format as the Value to offset argument.

Non-positive behavior

The optional Non-positive behavior argument determines what the LEAD function returns if the Offset amount argument is negative or zero.

The keywords for the Non-positive behavior argument are:

  • NONSTRICT, which returns the offset Value to offset if the Offset amount argument is positive, negative, or zero
  • SEMISTRICT, which returns the offset Value to offset if the Offset amount argument is positive or zero
  • STRICT, which returns the offset Value to offset only if the Offset amount argument is positive

If you omit the Non-positive behavior argument, the LEAD function defaults to the NONSTRICT keyword.

Constraints

The LEAD function has these constraints:

  • The result line item must be the same format as the Value to offset and Substitute value arguments.
  • The result line item must use the same time range as the Value to offset and Substitute value arguments.
  • The time range of any line items used as arguments must match the time range of the result line item. If the time range does not match, the formula is rejected. For more, see Time and Date Functions.

Excel equivalent

The LEAD function has no Excel equivalent.

Examples

General examples

These examples highlight the general results of the LEAD function.

A simple example of the LEAD function. As 2 is used for the Offset amount argument, the function returns the value from two periods in the future for each cell.

LEAD(x,2,0)   

In this example, LEAD 1 line item contains the above formula. It returns the value from the x line item from two periods after each cell. If two periods after a cell is outside of the module’s time range, the formula returns the Substitute value of 0, as seen in the Nov 20 and Dec 20 columns. The function does not contain the Non-positive behavior argument, so the behavior defaults to NONSTRICT.

An  example of the LEAD function. As 2 is used for the Offset amount argument, the function returns the value from two periods in the future for each cell. However, the Substitute value argument is also provided, so these are used for the final two periods in the module, where there are no future periods.

LEAD(x,2,z)

In this example, the LEAD 2 line item contains the above formula. It returns the value from the x line item from two periods after each cell. If two periods after a cell is outside of the module’s time range, the formula returns the Substitute value contained in the z line item. This means the formula returns the values of 1,100 and 1,200 contained in the Nov 20 and Dec 20 columns for the z line item. The function does not contain the Non-positive behavior argument, so the behavior defaults to NONSTRICT.

An example of the LEAD function. Arguments with varying values are used for each pattern, so the results have no simple pattern.

LEAD(x,y,z)

In this example, the LEAD 3 line item contains the above formula. It returns the value from the x line item for the period the y line item specifies. If the y line item specifies a period outside of the module's time range, the formula returns the Substitute value contained in the z line item. This means the formula returns the value of 1,200 contained in the Dec 20 column for the z line item. The function does not contain the Non-positive behavior argument, so the behavior defaults to NONSTRICT.

Non-positive behavior keyword examples

These examples highlight the effects of the three different keywords for the Non-positive behavior argument. The module for the examples uses the number-formatted line item y for the Offset amount argument. As the line item contains positive, negative, and zero values, it highlights the effect of the Non-positive behavior argument. In the screenshot below, cells that use the Substitute value are conditionally formatted as yellow.

Three different LEAD formulas demonstrate the effect of the NONSTRICT,  SEMISTRICT, and STRICT keywords. This results in a clear pattern, which is highlighted by conditional formatting.
FormulaDescription
LEAD(x, y, z, NONSTRICT)

This example uses the NONSTRICT keyword for the Non-positive behavior argument. The NONSTRICT keyword enables the use of positive, negative, and zero values for the Offset amount argument.

This means that this formula returns the offset value of x if y is positive, negative, or zero. However, if y specifies a period outside of the module’s time range, the formula returns the value of z.

LEAD(x, y, z, SEMISTRICT)

This example uses the SEMISTRICT keyword for the Non-positive behavior argument. The SEMISTRICT keyword enables the use of positive and zero values for the Offset amount argument.

This means that this formula returns the offset value of x if y is positive or zero. If y is negative or specifies a period outside of the module’s time range, the formula returns the value of z.

LEAD(x, y, z, STRICT)

This example uses the STRICT keyword for the Non-positive behavior argument. The STRICT keyword only enables the use of positive values for the Offset amount argument.

This means that this formula returns the offset value of x if y is positive. If y is negative, zero, or specifies a period outside of the module’s time range, the formula returns the value of z.

Similar functions