Forum Discussion

egspen2's avatar
egspen2
Copper Contributor
Oct 10, 2020

Monthly headcount based on hire date formula

I am looking to take an employee roster and, using a formula, put a "Yes" if the employee was active as of the respective month (based on comparing the hire date to the last day of the month in quest...
Formulas and Functions
Zeelynn's avatar
Zeelynn
Copper Contributor
Oct 29, 2020
How would you do a formula to grab the employees that were hired and rehired on or before a certain but excluded the ones that were terminated before that date?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 29, 2020

Zeelynn 

In general that's much more complex tax which is better to do with database or Excel tables whic imitates database. Tables could be

- departments: ID and name

-employees: ID, name, perhaps other static information like birthday

- employees history: date, employee ID, department ID (in case of change), within it hiring and termination dates

- calendar with each date for entire history

 

Above is simplified, could be bit more complex

 

After that Power Query itself or in combination with data model operations could generate desired reports.

 

Less reliable way is with formulas, but least you need to have termination date in your records.

  • zrob94's avatar
    zrob94
    Copper Contributor
    Feb 07, 2022

    SergeiBaklan Hi i have a smiliar question trying to get monthly headcount for a particular department i have start dates and leave dates 

     

    Similar data to the person below but also leave dates.

    I dont know what countifs function to use to get the monthly data for example all employees at X department up until 31st January 2022. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 07, 2022

      zrob94 

      You need to define more carefully what the "monthly headcount" is.

      For example, in department you had person who quit on 18 January 2022 and another person joined the company and this department on 14 January 2022.

       

      Thus you have 1 person till 14th, 2 persons from 14th to 18th and 1 person from 18t till end of January. You may calculate number of persons on first of each month, or average, or on the end of each month, or use other combinations. Each has pros and cons. Perhaps on the end of the month is most common, such situation when you have 1 person during entire month, one more joined on 31st and we have headcount in this month = 2 are considered as acceptable.

      Logic could be like

      =SUMPRODUCT(
   (Staff[Start Date] <= [@Month] ) *
   ( ( Staff[Quite Date] >= [@Month] ) +
     ( Staff[Quite Date] = "" )
   ) )

       

Resources