Forum Discussion
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...
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
Super User
Super UserHi 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
SeanCommunity Champion
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] ) ) ) )
