Forum Discussion

HRAnaly123's avatar
HRAnaly123
Frequent Visitor
2 years ago

Human Resources - Head Count with Date Comparison

Hi Team, 

 

I have a rolling Head Count Formula that calculates the total number of active staff at any given Date. 


What I would like to do is compare the Head Counts for two different date peroids. 

 

Example, the total Head Count at 01/01/2023 & 01/01/2022, so I can identify differences between each year/month. 

 

Outcome Example below, I would ideally, like this in a Table. 

Division | 2024 Count | 2023 Count | People Growth % 

Risk       | 36               | 42                 | 6% 
Finance  | 27               | 25                | -2% 

 

My rolling Head Count:

RollingHC = CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]),
    FILTER( VALUES( 'ADP_CurrentActive'[Hire Date]), ADP_CurrentActive[Hire Date] <= MAX( DATETABLE_DIM[Date] ) ),
    FILTER( VALUES( ADP_CurrentActive[TERM_DATE]), OR( 'ADP_CurrentActive'[TERM_DATE] >= MIN( DATETABLE_DIM[Date]), ISBLANK( 'ADP_CurrentActive'[TERM_DATE]))))
 
The Data Model is very simple,

Employee Data Table: 
 
Employee ID | Division | Hire Date     | Termination Date
1123              | Finance | 01/01/2021 | 30/01/2024
1771              | Finance | 01/01/2021 | [Blank]

Date Table: 

Date | Year | Month | Quater. 

Model Relationships:
Both Hire Date and Termination Date, have relationships to the Date Table. 

DAX

3 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi HRAnaly123 

     

    Please share some sample date or pbix file with any sensitive information removed, along with information on how your data model is setup.

    • HRAnaly123's avatar
      HRAnaly123
      Frequent Visitor
      2 years ago

      Hi Talespin, 

      I ammended my original post to cover your requriements. 

      Thank you

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi HRAnaly123 

    "Example, the total Head Count at 01/01/2023 & 01/01/2022, so I can identify differences between each year/month."

    Sorry I couldn't figure out the logic for using just any date/month/year.  Below solution does it for CurrentYear Vs Previous Year. If you can share more example to help me understand what you want to do.

     

    I have only one relationship active betwene Calendar and Employee table on HireDate(One to many, single direction)

     

     

     

     

    Measure1

    ------------

    Active Employee Count =
    VAR _Date = CALCULATE(MAX('CALENDAR'[Date]) )

    RETURN CALCULATE( COUNT(Employee[EmployeeID]), 'CALENDAR'[Date] <= _Date, ISBLANK(Employee[TerminationDate]) || Employee[TerminationDate] > _Date )
     
    Measure2
    --------------
    People Growth =
    VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])
    VAR _PreviousYear = _Year - 1
    VAR _CurrentYrCount = CALCULATE( COUNT(Employee[EmployeeID]), 'CALENDAR'[YEAR] <= _Year, ISBLANK(Employee[TerminationDate]) || YEAR(Employee[TerminationDate]) > _Year )
    VAR _PreviousYrCount = CALCULATE( COUNT(Employee[EmployeeID]), 'CALENDAR'[YEAR] <= _PreviousYear, ISBLANK(Employee[TerminationDate]) || YEAR(Employee[TerminationDate]) > _PreviousYear )

    RETURN DIVIDE( (_CurrentYrCount - _PreviousYrCount), _PreviousYrCount)
     

     

     Sample data I used
    EmployeeIDDivisionHireDateTerminationDate
    1Finance2023-01-052023-01-05
    2HR2023-03-15 
    3IT2023-06-282024-01-15
    4Finance2023-08-15 
    5HR2023-05-222023-05-22
    6IT2023-10-16 
    7Finance2023-11-152023-11-15
    8HR2023-12-24 
    9IT2024-01-062024-01-06
    10Finance2024-02-062024-02-06
    11HR2024-01-19 
    12IT2024-01-222024-01-22
    13Finance2023-09-062023-09-06
    14IT2023-08-14 
    15HR2023-07-112023-07-11
    16Finance2023-10-11 
    17HR2023-04-062023-04-06
    18IT2023-01-052024-01-15
    19HR2023-03-152024-01-15
    20Finance2023-06-282024-01-15