Forum Discussion

MuhammadSiddiq's avatar
MuhammadSiddiq
Frequent Visitor
8 years ago

Employee Turnover

Hello,

 

I am needing help in clculating employee turnover to show monthly trend. The formula used to calculate turnover is like this, suppose if we want to calculate the turnover for the month of January then following formula can be used; 

 

Turnover for Janauary  = Employee left in Janauary /  Popoulation of janauary

Turnover for February  = Employee left in february /  Popoulation of janauary

Turnover for March      = Employee left in March /  Popoulation of janauary

Turnover for April         = Employee left in April /  Popoulation of April

Turnover for May          = Employee left in May /  Popoulation of April

Turnover for June          = Employee left in June /  Popoulation of April

Turnover for July           = Employee left in July /  Popoulation of July

Turnover for August      = Employee left in August /  Popoulation of July

 

and so on.....

 

If need the overall turnover for the whole year the we need to calculate by using following formula;

 

 

Turnover for year = Total number of Leaver during the year / Average (Population of Jan+ Population of April+population of July+ population of October)

 

 

I am unable to calculate this by using DAX, Can anybody help me out to calculate this..

 

 

Looking forward for solution

 

 

Thanks,

Muhammad Siddiq

 

Need Help

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    This looks like an interesting problem. Can you provide some example data and expected output for this? This would really help understand you problem.

    • MuhammadSiddiq's avatar
      MuhammadSiddiq
      Frequent Visitor
      8 years ago

       

      Hey Smoupre,

      Thanks for replying and  i tried alot but failed to get the output am pasting a sample data for your reference.

       

      MonthActive EmployeesSeparated EmployeesTurnover Formula Turnover %
      Jan1001010/10010%
      Feb10555/1005%
      March10633/1003%
      April10922/1062%
      May1121515/10614%
      June1152020/10618%
      July11855/1184%
      Aug1201515/11813%
      Sept1212525/11821%
      Oct1252121/12517%
      Nov1301818/12514%
      Dec15099/1257%
      Yearly Turn Over(jan+April+July+Oct)/4148148/(100+109+118+125)/48%

       

       

      This is just to give you an idea. Looking forward for the solution, as my presentation is due bby next week and i got stuck with this dynamic formula.

       

       

      Regards,

      Muhammad Siddiq

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        MuhammadSiddiq,

        Create the following columns in your table.

        Quarter = IF(OR(OR(Table[Month] = "Jan",Table[Month] = "Feb"),Table[Month] = "March"),1,IF(OR(OR(Table[Month] ="April",Table[Month] ="May"),Table[Month] ="June"),2,IF(OR(OR(Table[Month] = "July",Table[Month] ="Aug"),Table[Month] ="Sept"),3,4)))
        MinEmployee = CALCULATE(MIN(Table[Active Employees]),FILTER(Table,Table[Quarter]=EARLIER(Table[Quarter])))
        Turnover = Table[Separated Employees]/Table[MinEmployee]


        Then create the following measures in your table.

        sum employees = SUM(Table[Separated Employees])
        summinemployee = SUM(Table[MinEmployee])/3
        Year Turn over = DIVIDE( [sum employees]/[summinemployee],MAX(Table[Quarter]))





        Regards,
        Lydia