Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CalexUK
Frequent Visitor

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 4Warranty Executivexpbt6ml20/01/198322/06/2022
Brand 1Parts Advisorxppz4bm01/07/1987 
Brand 4Parts Advisorxpx5kl422/09/1987 
Brand 3Warranty Executivexpw5xut05/09/198814/04/2023
Brand 1Technicianxpz6fsc27/11/1989 
Brand 5Technicianxpbopth01/10/199030/06/2022
Brand 1Technicianxpej65401/10/1990 
Brand 5Technicianxplxe0a22/03/1991 
Brand 5Service Advisorxpc9w4h01/08/1992 
Brand 3Service Advisorxphjrf918/01/1993 
Brand 1Technicianxpi66ot07/06/1993 
Brand 5Service Advisorxpmavqq15/03/1995 
Brand 4Technicianxpoartq05/09/1995 
Brand 4Technicianxpgwzrg01/01/1996 
Brand 4Technicianxpj2ynr06/05/1996 
Brand 4Technicianxpg4fhv02/06/1997 
Brand 4Technicianxpqgztl19/05/1998 
Brand 4Technicianxp2x2x401/09/1998 
Brand 2Technicianxp8e0sf10/05/1999 
Brand 5Sales Managerxpxmr8x04/01/2000 
Brand 1Technicianxpcun5b12/01/2000 
Brand 5Technicianxpnvlbl03/04/2000 
Brand 1Technicianxpet9g626/04/2000 
Brand 1Parts Advisorxpwbw9205/06/2000 
Brand 4Technicianxp408vy09/06/2000 
Brand 1Parts Managerxpgnezh01/08/2000 
Brand 1Technicianxpomn4b04/09/2000 
Brand 5Technicianxp9hb6y01/10/2000 
Brand 1Technicianxpmq4lq09/11/200009/04/2022
Brand 4Technicianxprhba408/01/2001 
Brand 1Sales Executivexp2ct6x01/02/2001 
Brand 3Sales Managerxp9g9j101/03/2001 
Brand 1Service Advisorxpesbbi02/07/2001 
Brand 4Technicianxptmkhj20/08/2001 
Brand 3Service Advisorxpt8trx17/09/200114/07/2022
Brand 1Technicianxp66zur26/11/2001 
Brand 4Technicianxpbki8h21/01/2002 
Brand 4Technicianxpb92nu25/02/2002 
2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1683516691935.png

 

Jihwan_Kim_2-1683517180486.png

 

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

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


amitchandak
Super User
Super User

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.