# SHIFT function

• 190 views

Userlevel 1
• Employee

## Description

Returns an expression of type Dimension List where values are items from the original List shifted by a positive or negative integer offset.

## Syntax

`SHIFT(source, offset)`
• `source` is the source expression of type Dimension List that should be shifted.
• `offset` is the offset of the shift: use a positive number to shift ahead by N items, or a negative number to shift behind by N items.

## Return type

`Dimension List`

## Example

Formula = `Employee.StartingMonth`

Alice Bob Carl Dave
Formula Mar Jun Jan Apr

Formula = `SHIFT(Employee.StartingMonth, 1)`

Alice Bob Carl Dave
Formula Apr Jul Feb May

Formula = `SHIFT(Employee.StartingMonth, -2)`

Alice Bob Carl Dave
Formula Jan Apr   Feb

The SHIFT function is powerful when the offset is dependent on another Dimension/List property.

Offset Metric (Integer Metric defined on team Dimension)

Offset
Sales 3
R&D 1
HR 1

Example on list:
Here, an 'Employee' List, 'Team' and 'Start Month' Properties are Manual inputs.  'Full Ramp-up month' is a Property computed with the formula:
`SHIFT(Employee.'Start Month','Offset'[by: Employee.Team])`

Team Start Month Full Ramp-up month
Alice Sales Apr Jul
Bob R&D Jan Feb
Carl HR Feb Mar

Example on metric:

FTE by team

Jan Feb Mar Apr May Jun
Sales 1.5

2

2

3

3 3
R&D 5 6 8 8 8 8
HR   1 1 1 1 1

Offset-ed FTE

`'FTE by team'[by constant: SHIFT(Month[add:team] , -'Offset')]`

Jan Feb Mar Apr May Jun
Sales

1.5

2 2
R&D   5 6 8 8 8
HR     1 1 1 1

I’ve found myself combining this with the `SELECT` modifier and it works pretty well. I had a metric with this formula `Billings[SELECT: Month - 1]` to shift the value one month but had to change it so that the shifting value was dependent on the context. I just created a `Billing Lag` metric (type integer) and modified my formula to `Billings[SELECT: SHIFT(Month, 'Billing Lag')]`.