Hires and Terminations Waterfall

[Schneider879]

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!

[MFelix]

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 · Accepted solution]

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]

@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

[v-piga-msft]

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

[Sean]

---

---

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

[MFelix]

Hi @Sean,

As I told you befor you were correct regarding my calculations I looked at the formula I provided and put in two errors:

1. Format should be YYYYMM to keep number bigger during time
2. On the last FORMAT I used I have place MMYYYYY - Five fields for the YEAR so incorrect values I only looked at July and the number was correct.

Check the revised formula below, all months already show up and if you can put it against your chart to compare results I would appreciate.

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

Once again I believe that your answer is better but just giving options because some times the information is not with dates and is with quarters os codes or something else, and people can get the result without adding additonal columns in the models.

Very happy to have this type of discussion to help me and other improve in DAX skills.

Regards,

MFelix

Regards,

MFelix

[MFelix]

Hi ,

You are correct I will remove my second answer so others cannot be mislead on reading the thread .

If your answer isn't marked as correct in the next days I will do it since it's correct.
Regards,
MFelix