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 = [(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 ?

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.

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    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

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 +

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

        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.