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 ...
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
