Human Resources - Head Count with Date Comparison

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