I have a table (EMP) of all employees as like the example below.
| EMP_ID | Department | Dep_Start_Date | Dep_End_Date |
|---|---|---|---|
| 1 | A | 2019-05-01 | 2099-12-31 |
| 2 | A | 2019-10-01 | 2021-04-10 |
| 2 | B | 2021-04-11 | 2099-12-31 |
| 3 | C | 2022-09-01 | 2099-12-31 |
In addition to this I have a date table (CAL).
To count the number of employees I use the following measure:
Employees = CALCULATE(DISTINCTCOUNT(EMP[EMP_ID]),EMP[Dep_Start_Date]<=min(CAL[Date]) && EMP[Dep_End_Date]>=max(CAL[Date])))
The problem is that when I view this in a table showing both years and months everything is fine. But in a table showing only years I get a gap where employee #2 switched departments.
Employee #2 gets a gap in 2021 when i try to view the number of employed in a specific year:
| EMP_ID | 2019 | 2020 | 2021 | 2022 | 2023 |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 0 | 1 | 1 |
| 3 | 0 | 0 | 0 | 1 | 1 |
The reason for this is that the measure calculates that there is one row for when the employee #2 worked 2019-10-01 to 2021-04-10 in deparment A that gets shown in 2019 and 2020. Additionally there is a row for employee #2 between 2021-04-11 to 2099-12-31 that gets a value of 1 for the years 2022 and 2023.
But for 2021 and the employee #2 none the two rows do not meet both the criteria <=min(CAL[Date]) && >=max(CAL[Date]).
Simply put the formula does not pick the min start date and the max end date for that employee.
I have tried various SUMMARIZE, COUNTX etc to no avail.
Is there a solution to this?
Try this:
Employees2 = CALCULATE(
DISTINCTCOUNT(EMP[EMP_ID]),
( EMP[Dep_Start_Date]<=min(CAL[Date]) || EMP[Dep_Start_Date]<=max(CAL[Date]) ) &&
( EMP[Dep_End_Date]>=min(CAL[Date]) || EMP[Dep_End_Date]>=max(CAL[Date]) )
)