To show the movement between Levels for the chosen FY

[Syndicate_Admin]

Hi All,

I have a requirement in which I need to track the movement of employees between Levels (Level 1 Level2) for the chosen financial year. Below are the cases for which I need to calculate the no of employees.

1) No change : No change in Levels between selected FY and Previous FY

2) Level1 -> Level2 and Level2 -> Level1 : Level changes between selected FY and Previous FY

The sample data is given below.

EmpCode	FY	Level
1	3/31/2019	Level1
1	3/31/2020	Level1
1	3/31/2021	Level1
2	3/31/2019	Level1
2	3/31/2020	Level1
2	3/31/2021	Level2
3	3/31/2019	Level2
3	3/31/2020	Level2
3	3/31/2021	Level2
4	3/31/2019	Level1
4	3/31/2020	Level1
4	3/31/2021	Level2
5	3/31/2019	Level1
5	3/31/2020	Level2
5	3/31/2021	Level1

Desired Results

Selected FY : 03/31/2021

Movement	No of Employees
No Change	2
Level1 -> Level2	2
Level2 -> Level1	1

Selected FY : 03/31/2020

Movement	No of Employees
No Change	4
Level1 -> Level2	1
Level2 -> Level1	0

Will appreciate if anyone can help me out on this requirement.

Thanks

Bend Phadnis

India

[Syndicate_Admin]

Thanks a lot, exactly as I wanted.

[lbendlin · Accepted solution]

You can do that with three calculated columns

No Change = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c),"e",[EmpCode],"l",[Level])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p),"e",[EmpCode],"l",[Level])
return countrows(INTERSECT(ct,pt))

1 to 2 = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c && Moves[Level]="Level2"),"e",[EmpCode])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p && Moves[Level]="Level1"),"e",[EmpCode])
return countrows(INTERSECT(ct,pt))

2 to 1 = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c && Moves[Level]="Level1"),"e",[EmpCode])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p && Moves[Level]="Level2"),"e",[EmpCode])
return countrows(INTERSECT(ct,pt))

And the result would look like this:

or if you want to put the values on rows