Staff Turnover Solution

[CalexUK]

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

[Jihwan_Kim]

Hi,

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] )
)

[amitchandak]

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