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

LEAD returns a value from a period in the future.

You could use LEAD to calculate how this month's earnings compare to next year's projected monthly earnings.

## Syntax

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

## Arguments

Note: The LEAD keywords are NONSTRICT, SEMISTRICT, or STRICT.

 Argument Data Type Description Value to offset (required) Number, Boolean, date, time period, list, or text Value to replace with a value from a different time period Offset amount (required) Number Number of periods forward, from which to retrieve a value. Positive values refer to future periods, negative to past periods and Zero is the current period. Substitute value (required) Mirrors Value to offset data type Value to apply if theOffset amount specifies a period outside of the model's time range. Also used for non-positive offsets if you use the SEMISTRICT or STRICT keywords for the Non-positive behavior argument. Non-positive behavior (optional) Keyword Determines if the LEAD function should use the Substitute value instead if the Offset amount is negative or zero.

## Syntax example

LEAD(Value to offset, 2, 0)

In this example, the LEAD 1 line item returns the value 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 June column. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

 Jan Feb March April May June Value to offset 1 2 3 4 5 6 LEAD 1 3 4 5 6 0 0

## Additional information

### Keywords for non-positive behavior argument:

The LEAD mode is set by the keywords, NONSTRICT, SEMISTRICT, or STRICT. The mode controls whether LEAD applies to the whole time range, the current period and all future periods, or future periods only.

• NONSTRICT: returns the Value to offset if the Offset amount is positive, negative, or zero.
• SEMISTRICT: returns the Value to offset if the Offset amount is positive or zero.
• STRICT: returns the Value to offset if the Offset amount is positive. In STRICT mode, LEAD applies to the future, and not to current periods. The fill value is returned if either shift < 0 or the future period is beyond model time range.
• If the period LEAD specifies is outside of the module's timescale, LEAD returns the substitute value.
• If a keyword is not included, the behavior defaults to NONSTRICT.

Note: LEAD is the opposite of LAG. Yet the two functions can achieve the same result. If the offset amount of LEAD is negative, then the result is the same as LAG with a positive offset.

## Detailed examples

### Example 1

LEAD(Value to offset, 2, Substitute value)

In this example, the LEAD 2 line item returns 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. The formula returns the values of 500 and 600 contained in the May and June columns for the Substitute value. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

 Jan Feb March April May June Value to offset 1 2 3 4 5 6 Substitute value 100 200 300 400 500 600 LEAD 2 3 4 5 6 500 600

### Example 2

LEAD(Value to offset, Offset amount, Substitute value, [non-positive behavior]) In this example, the LEAD 3 line item returns the Substitute value for the period the Offset amount specifies. If the Substitute value specifies a period outside of the module's time range, the formula returns the Substitute value. This means the formula returns the value of 600 contained in the June column for the Substitute value. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

 Jan Feb March April May June Value to offset 1 2 3 4 5 6 Offset amount 0 -1 0 1 0 1 Substitute value 100 200 300 400 500 600 LEAD 3 1 1 3 5 5 600

### Example 3

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

In this example, you can see how the different keywords for the Non-positive behavior change the results.

 Jan Feb March April May June Value to offset 1 2 3 4 5 6 Offset amount 0 -1 0 1 0 1 Substitute value 100 200 300 400 500 600 LEAD SEMISTRICT 1 200 3 5 5 600 LEAD STRICT 100 200 300 5 500 600 LEAD NONSTRICT 1 1 3 5 5 600