SHIFT function

  • 14 January 2022
  • 1 reply

Userlevel 1



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



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



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


Advanced Examples


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

Offset Metric (Integer Metric defined on team Dimension)

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




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




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


See also


1 reply

Userlevel 4
Badge +3

Super interesting!

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')].

It works great!