SHIFT function

  • 14 January 2022
  • 1 reply
  • 190 views

Userlevel 1

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

 

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)
 

  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

 


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!

Reply