Forum Discussion
Count unique values with multiple results
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
2 Replies
frankhofmans , refer if my HR blog or attached file after signature can help
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
