Human Resources - Head Count with Date Comparison

[HRAnaly123]

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. 

[talespin]

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

1	Finance	2023-01-05	2023-01-05
2	HR	2023-03-15
3	IT	2023-06-28	2024-01-15
4	Finance	2023-08-15
5	HR	2023-05-22	2023-05-22
6	IT	2023-10-16
7	Finance	2023-11-15	2023-11-15
8	HR	2023-12-24
9	IT	2024-01-06	2024-01-06
10	Finance	2024-02-06	2024-02-06
11	HR	2024-01-19
12	IT	2024-01-22	2024-01-22
13	Finance	2023-09-06	2023-09-06
14	IT	2023-08-14
15	HR	2023-07-11	2023-07-11
16	Finance	2023-10-11
17	HR	2023-04-06	2023-04-06
18	IT	2023-01-05	2024-01-15
19	HR	2023-03-15	2024-01-15
20	Finance	2023-06-28	2024-01-15

[talespin]

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]

Hi Talespin, 

I ammended my original post to cover your requriements. 

Thank you