Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Viraj Phadnis
India
Solved! Go to Solution.
Puede hacerlo con tres columnas calculadas
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))
Y el resultado se vería así:
o si desea poner los valores en filas
Thanks a lot, exactly as I wanted.
Puede hacerlo con tres columnas calculadas
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))
Y el resultado se vería así:
o si desea poner los valores en filas
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
1 | |
1 |