Forum Discussion

Schneider879's avatar
Schneider879
Frequent Visitor
7 years ago
Solved

Hires and Terminations Waterfall

Hello,

 

I'm hoping you all have some suggestions for how I could accomplish creating a creating a chart that will essentially count a hire as a +1 and a termination as a -1, giving me the net headcount each day/month/quarter. I have a data source that includes both dates of hires and dates of termination for individual employees. Thanks!

  • Sean's avatar
    Sean
    7 years ago

    Using MFelix’s sample data shouldn’t you get 4 for July 2017?

    Try this Measure...

    Employee Count =
CALCULATE (
    COUNT ( Table[Employee] ),
    FILTER (
        Table,
        Table[Start Date] <= LASTDATE ( DateTable[Date] )
            && (
                Table[EndDate] >= FIRSTDATE ( DateTable[Date] )
                    || ISBLANK ( Table[EndDate] )
            )
    )
)

8 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Schneider879,

     

    Assuming that you have a simple table like the one below:

     

    EmployeeStart DateEnd Date

    A 01 July 2018  
    B 01 February 2016 20 May 2018
    C 06 July 2017  
    D 06 February 2018  
    E 05 March 2018 30 July 2018
    F 06 June 2017 30 July 2018
    G 02 December 2016  

     

    Create a date table and then add the following measure:

     

    EmployeeCount =
VAR First_Date =
    MIN ( DimDate[Date] )
VAR Last_date =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNT ( Start_End_Date[Employee] );
        FILTER (
            Start_End_Date;
            Start_End_Date[Start Date] <= First_Date
                && Start_End_Date[End Date] = BLANK ()
        )
    )
        + CALCULATE (
            COUNT ( Start_End_Date[Employee] );
            FILTER (
                Start_End_Date;
                Start_End_Date[Start Date] <= First_Date
                    && Start_End_Date[End Date] >= Last_date
            )
        )

    Should give the expected result

     

     

     

    Regards.

    MFelix

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Using MFelix’s sample data shouldn’t you get 4 for July 2017?

      Try this Measure...

      Employee Count =
CALCULATE (
    COUNT ( Table[Employee] ),
    FILTER (
        Table,
        Table[Start Date] <= LASTDATE ( DateTable[Date] )
            && (
                Table[EndDate] >= FIRSTDATE ( DateTable[Date] )
                    || ISBLANK ( Table[EndDate] )
            )
    )
)
      • Schneider879's avatar
        Schneider879
        Frequent Visitor
        7 years ago

        Sean Yes based on MFelix's data we would expect to get a value of 4 since two people were hired in 2016, one in June 17, and one in July 17. I'll try out your formula today and see if it gives the expected result. Thanks