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] )
            )
    )
)
Sean's avatar
Sean
Community 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...

 

MFelix's avatar
MFelix
Super User
7 years ago

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