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.
Hello,
I have a employee table like this :
Employee_code l Start_date l End_Date l Actif (Yes or no )
I want to calculate the Annual turnover which is this formula :
Annual turnover = [(Number of employees that left + Number of arrivals N)/2] / Number of employees at january first
The issue that i see is, we can't have a measure that calculate number of employees during a dynamic period that can be controlled by a filter, i don't know if i expressed my self correctly,
what's the closest solution that we can have ?
Solved! Go to Solution.
Hello @Anonymous
Take a look at the attached file. I think the measure will return what you are looking for.
Turnover = VAR FirstOfYear = STARTOFYEAR ( Dates[Date] ) VAR LastOfYear = ENDOFYEAR ( Dates[Date] ) VAR JanFirstHC = CALCULATE( COUNTROWS(employees), FILTER ( employees, employees[start_date] <= FirstOfYear && ( employees[end_date] > FirstOfYear || ISBLANK(employees[end_date]) ) ) ) VAR NewHires = CALCULATE( COUNTROWS( employees), employees[start_date] >= FirstOfYear && employees[start_date] <= LastOfYear ) VAR Leavers = CALCULATE( COUNTROWS( employees), employees[end_date] >= FirstOfYear && employees[end_date] <= LastOfYear ) VAR AnnualTurnover = ((Leavers + NewHires)/2) / JanFirstHC RETURN FORMAT(AnnualTurnover,"PERCENT")
I put each variable from above into it's own mesure just so you could see what it is calculating.
Hello @Anonymous
Take a look at the attached file. I think the measure will return what you are looking for.
Turnover = VAR FirstOfYear = STARTOFYEAR ( Dates[Date] ) VAR LastOfYear = ENDOFYEAR ( Dates[Date] ) VAR JanFirstHC = CALCULATE( COUNTROWS(employees), FILTER ( employees, employees[start_date] <= FirstOfYear && ( employees[end_date] > FirstOfYear || ISBLANK(employees[end_date]) ) ) ) VAR NewHires = CALCULATE( COUNTROWS( employees), employees[start_date] >= FirstOfYear && employees[start_date] <= LastOfYear ) VAR Leavers = CALCULATE( COUNTROWS( employees), employees[end_date] >= FirstOfYear && employees[end_date] <= LastOfYear ) VAR AnnualTurnover = ((Leavers + NewHires)/2) / JanFirstHC RETURN FORMAT(AnnualTurnover,"PERCENT")
I put each variable from above into it's own mesure just so you could see what it is calculating.
there is also a little issue with the turnover calculations : it's supposed to be new hires + Leavers but since Leavers is negative its calculation is wrong, i think in the formula we should write -Leavers instead of +leavers so the - can turn into a +
@Anonymous
Yeah, I wasn't sure which way you wanted those to sum, sounds like you got it the way you need now. You only need to add the one big measure. I just added the others so you could see the numbers. They might be useful to have though.
Thanks, i just want to add for the other users that will see the post, to have the correct turnover formula to work in a double axis as a percentage, Leave it at "Return AnnuelTurnover" without the "percentage" format then go to model and change the format to percentage, you also need to delete the - in the leavers formula
I updated the leavers portion of my measure in the original post.
Thank you @jdbuchanan71 it worked perfectly,
however there is only one issue, when i added the 6 measures and saved closed the report, i coulnd't open it this morning, it says file corrupted impossible to open, luckily i have it published online, i will repeat all the steps and try again.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |