Count unique values with multiple results

[frankhofmans]

hi PBI experts,

i have the following question:

i have a table with employee ID, start contract and end contract (or empty if the employee is still working):

EMP ID	START	END
001	1 jan 2021	1 feb 2022
002	1 mar 2021
003	1 apr 2021
004	1 mar 2021	1 may 2021
004	1 jun 2021
005	1 jun 2021	10 jun 2021
005	1 sep 2021
006	1 oct 2021	1 dec 2021
007	1 oct 2021

i want to count the number of unique employees that who were hired in a certain period, and the number of employees who left the company. But in a few cases, the same person left the company and is hired back (or gets a new contract).

So, in Q1 2021, the number of unique employees hired is 3 (001, 002, 004), and the number of unique employees left is 1 (001). In Q2, the number of unique employees hired is 1 (005). Employee 004 was already hired in a previous quarter and was still employed in Q2 (in april). 

Employee ID 005 started in Q2, but also left the company in Q2 (so counted as 1 in both started and left). But if i change the date filter from Quarter to Year (2021), 005 has to count as 1 (he was already hired in 2021, before getting a new contract).

Quarter:

Q	Hired	Left
Q1	3 (001,002,004)	1 (001)
Q2	1 (005)	1 (005)
Q3	1 (005)
Q4	2 (006,007)	1 (006)

Year:

Year	Hired	Left
2021	7	2 (001,006)

How can i solve this with a measure?

Thanks in advance!

Regards, Frank

[amitchandak]

@frankhofmans , refer if my HR blog or attached file after signature can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[frankhofmans]

hi Amitchandak!

Thanks for your reply. Your file is very helpfull, but i have troubles with employees that have multipe contracts. I have the following date table:

EMPlOYEE ID	START	END
E001	1-feb-21	28-feb-21
E001	1-mrt-21	14-mei-22
E002	1-mrt-21	31-mrt-21
E002	1-apr-21
E003	1-apr-21	1-aug-22
E004	1-apr-21
E005	1-mei-21	31-mei-21
E005	1-jun-21
E006	1-jun-21
E007	1-jul-21
E008	1-jul-21	31-jul-21
E008	1-aug-21
E009	1-jul-21	31-jul-21
E009	1-aug-21	1-jul-22
E010	1-aug-21	31-aug-21
E010	1-sep-21
E011	1-sep-21
E012	1-okt-21	1-dec-21
E013	1-jan-22
E014	1-jan-22	1-jun-22

I want to create the following outcome:

	2021	2021	2021	2021	2022	2022	2022
	Q1	Q2	Q3	Q4	Q1	Q2	Q3
# Unique employees begin of period	0	2	6	11	11	13	11
# Unique employees started in period	2	4	5	1	2	0	0
# Unique employees ended in period	0 (E001 + E002 has a new contract in the same period)	0 (E005 has a new contract in the same period)	0	1	0	2	2
# Unique employees end of period	2	6	11	11	13	11	9
# Unique employees active in period (# of unique employees with at least 1 working day in selected period)	2	6	11	12	13	13	11

And with the possibility to change the date in year of month:

	2021	2022
# Unique employees begin of period	0	11
# Unique employees started in period	12	2
# Unique employees ended in period	1	4
# Unique employees end of period	11	9
# Unique employees active in period	12	13

So when an employee is already employed, and he/she has a new contract in a certain periode, it doesn't count as 1, but as 0. Or when a employee is hired and get a 2nd contract (from a monthly contract to a year contract) it has to count as 1.

Do you have an idea to fix this?

Many thanks!

Regards, Frank