DAX to count number of employees by the end of a year

[vananh_nguyen]

I'm having trouble understanding why the DAX measure doesn't return the expected results.

I have 2 tables:

- Calendar Years which lists the years from 2019 - 2023

- Hire: includes transactions when an employee was first hired or rehired into a high-ranking position that we tracked (say Position A). This table was previously merged with another table, Resignation, indicating the Projected Resignation date from position A, NOT from the organization. The Projected Resignation Date can include a date in the past even though an employee is still active in position A. In this case, it means that the employee was rehired into position A, and the table still contains the old record of the last resignation date from position A. The table has a Termination Date to indicate when the employee left the organization. The Termination Date is null if the employee is active.

Hiring Table - Example of an employee

Hire Year	Hire Date	Employee ID	Termination Date	Projected Resignation Date
2006	01-15-2006	12345	null	06-20-2009
2017	03-31-2017	12345	null	06-20-2009

Relationships:

- The Calendar and Hiring table joined together using Hire Year.

DAX Measure and Problem:

- I used the following measure to calculate the number of active employees for position A by the end of each year (2019-2023). Using the above example, I would expect there will be 2 counts for Employee ID 12345, however, the table only shows 1 count.  1 count is the correct result, I just wanted to understand why it turned out to be 1 and not 2. I'm still fairly new to DAX so perhaps I misunderstood some logic here. Thank you so much for your help!

Active Employee at End of Year = 
VAR CurrentYear = MAX('Calendar Years'[Year])  // Get the current year from the calendar year table
VAR EmpCount =
    CALCULATE(
        COUNTROWS(
            CALCULATETABLE( 'Hire', 'Hire'[Hire Year] <= CurrentYear, ALL('Calendar Years'[Year]))
        ),
        (ISBLANK('Hire'[Projected Separation Date]) || 'Hire'[Projected Separation Date] > DATE(CurrentYear, 12, 31) ||
        ('Hire'[Projected Separation Date] <= DATE(CurrentYear, 12,31) && 'Hire'[Action Start Date] > 'Hire'[Projected Separation Date])
        )
    )
RETURN
EmpCount

This is the table I have as a result of the above DAX measure. The 'Year' column is from the 'Calendar Year' table.

Year	Number of Employees at End of Year
2019	1
2020	1
2021	1
2022	1

[lbendlin · Accepted solution]

You have a granularity mismatch between employee details (day level) and reporting needs (year level).  As you indicate you can solve that by affixing your year to a date (last day of year).  Most likely you need to keep these tables disconnected. But you should not need to have separate hire and resignation tables. That should be a single table with employee ID, start date and (optional) end date.  Then you can do a DISTINCTCOUNT on the employee ID (assuming they get the same ID when they get rehired).