cancel
Showing results 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

## 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.

 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

India

1 ACCEPTED SOLUTION
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:

or if you want to put the values on rows

2 REPLIES 2

Thanks a lot, exactly as I wanted.

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:

or if you want to put the values on rows

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors