Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

To show the movement between Levels for the chosen FY

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.

EmpCodeFYLevel
13/31/2019Level1
13/31/2020Level1
13/31/2021Level1
23/31/2019Level1
23/31/2020Level1
23/31/2021Level2
33/31/2019Level2
33/31/2020Level2
33/31/2021Level2
43/31/2019Level1
43/31/2020Level1
43/31/2021Level2
53/31/2019Level1
53/31/2020Level2
53/31/2021Level1

Desired Results

Selected FY : 03/31/2021

MovementNo of Employees
No Change2
Level1 -> Level2
2
Level2 -> Level1
1

Selected FY : 03/31/2020

MovementNo of Employees
No Change4
Level1 -> Level21
Level2 -> Level10

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

Thanks

Bend Phadnis

India

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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:

lbendlin_0-1630810061080.png

or if you want to put the values on rows

lbendlin_2-1630810343679.png

 

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

Thanks a lot, exactly as I wanted.

lbendlin
Super User
Super User

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:

lbendlin_0-1630810061080.png

or if you want to put the values on rows

lbendlin_2-1630810343679.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.