Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate employee turnover

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 = [(Num...
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

I updated the leavers portion of my measure in the original post.