Forum Discussion
Staff Turnover Solution
I have seen and tried many solutions without succes, I am looking to build a Power Bi solution to show the Population By Month Year using the attached data, I have start Dates and End Dates for each user.
I want to build a line graph that shows the total population by each month of each year, then have another line showing how many users started then a third line to show how many users have left.
I already have a Calendar table called "Dates"
Below is a small sample of my table...
BrandJob RoleUser IxStart DateEnd Date
| Brand 4 | Warranty Executive | xpbt6ml | 20/01/1983 | 22/06/2022 |
| Brand 1 | Parts Advisor | xppz4bm | 01/07/1987 | |
| Brand 4 | Parts Advisor | xpx5kl4 | 22/09/1987 | |
| Brand 3 | Warranty Executive | xpw5xut | 05/09/1988 | 14/04/2023 |
| Brand 1 | Technician | xpz6fsc | 27/11/1989 | |
| Brand 5 | Technician | xpbopth | 01/10/1990 | 30/06/2022 |
| Brand 1 | Technician | xpej654 | 01/10/1990 | |
| Brand 5 | Technician | xplxe0a | 22/03/1991 | |
| Brand 5 | Service Advisor | xpc9w4h | 01/08/1992 | |
| Brand 3 | Service Advisor | xphjrf9 | 18/01/1993 | |
| Brand 1 | Technician | xpi66ot | 07/06/1993 | |
| Brand 5 | Service Advisor | xpmavqq | 15/03/1995 | |
| Brand 4 | Technician | xpoartq | 05/09/1995 | |
| Brand 4 | Technician | xpgwzrg | 01/01/1996 | |
| Brand 4 | Technician | xpj2ynr | 06/05/1996 | |
| Brand 4 | Technician | xpg4fhv | 02/06/1997 | |
| Brand 4 | Technician | xpqgztl | 19/05/1998 | |
| Brand 4 | Technician | xp2x2x4 | 01/09/1998 | |
| Brand 2 | Technician | xp8e0sf | 10/05/1999 | |
| Brand 5 | Sales Manager | xpxmr8x | 04/01/2000 | |
| Brand 1 | Technician | xpcun5b | 12/01/2000 | |
| Brand 5 | Technician | xpnvlbl | 03/04/2000 | |
| Brand 1 | Technician | xpet9g6 | 26/04/2000 | |
| Brand 1 | Parts Advisor | xpwbw92 | 05/06/2000 | |
| Brand 4 | Technician | xp408vy | 09/06/2000 | |
| Brand 1 | Parts Manager | xpgnezh | 01/08/2000 | |
| Brand 1 | Technician | xpomn4b | 04/09/2000 | |
| Brand 5 | Technician | xp9hb6y | 01/10/2000 | |
| Brand 1 | Technician | xpmq4lq | 09/11/2000 | 09/04/2022 |
| Brand 4 | Technician | xprhba4 | 08/01/2001 | |
| Brand 1 | Sales Executive | xp2ct6x | 01/02/2001 | |
| Brand 3 | Sales Manager | xp9g9j1 | 01/03/2001 | |
| Brand 1 | Service Advisor | xpesbbi | 02/07/2001 | |
| Brand 4 | Technician | xptmkhj | 20/08/2001 | |
| Brand 3 | Service Advisor | xpt8trx | 17/09/2001 | 14/07/2022 |
| Brand 1 | Technician | xp66zur | 26/11/2001 | |
| Brand 4 | Technician | xpbki8h | 21/01/2002 | |
| Brand 4 | Technician | xpb92nu | 25/02/2002 |
2 Replies
CalexUK , refer if the attached files or blog on similar topic can help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Hi,
Please check the below picture and the attached pbix file.
Population: = VAR _mindate = MIN ( Dates[Date] ) VAR _maxdate = MAX ( Dates[Date] ) VAR _populationtable = SUMMARIZE ( FILTER ( ALL ( Data ), Data[Start Date] <= _maxdate && OR ( Data[End Date] = BLANK (), Data[End Date] >= _mindate ) ), Data[Ix] ) RETURN COUNTROWS ( _populationtable )Start count: = COUNTROWS( VALUES(Data[Ix]) )End count: = CALCULATE ( COUNTROWS ( SUMMARIZE ( FILTER ( Data, Data[End Date] <> BLANK () ), Data[Ix] ) ), USERELATIONSHIP ( Dates[Date], Data[End Date] ) )
