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 head...
  • 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] )
            )
    )
)
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

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago
      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        7 years ago

         

        I still fail to see how you can say it produces the expected results?

         

        From July 2017 through January 2018 nobody is terminated !

        So you should stay at 4 yet your chart above dips to 3 for the Months - Aug, Sep, Oct, Nov

         

        Your chart also has no entry at all for January 2018 - should be 4 still - no one hired or terminated

        Then in February 2018 we add 1 - so we should be at 5 yet your chart show 2

         

        and so on...

         

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      Hi Schneider879,

       

      Have you solved your problem?

       

      If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

       

      If you still need help, please feel free to ask.

       

      Best  Regards,

      Cherry