Forum Discussion
Employee Start Date - Stop Date Ribbon Chart w/ Running Cumulative Total Employee Count
I'd like to create a Line & Clustered Column Chart having on the X-Axis a running list of dates representing the minimum or FIRSTDATE of all employee hires, up to the maximum or LASTDATE of all employee departures. The colums or ribbon (if using a ribbon chart) rises (+) and falls (-) with employee arrivals and departures. To this chart I'd like to add another data series - a running total line - which suggests I must use the 'Line & Clustered Column Chart'.
*Upudate: I've created a new table w/ one column, "Date", representing a serial listing of date records from my first employee's start date (FIRSTDATE) all the way to the most recently recorded (or forthcoming scheduled) departure date (LASTDATE).
I'm having trouble representing the arrivals as 'additions' or up-tick columns, and the departures as down-tick columns, with each column binned along the x-axis using the FIRSTDATE to LASTDATE column I've created above. I intend to depict these columns as GREEN and RED - green for arrivals, and red for departures.
If you think there's a better, more depictive way to show the rise and fall of a population, welcome your counsel.
6 Replies
v-lid-msftCommunity Support
Hi FruitBat ,
We can try to create following measure to get the change number of employee in the selected day.
Measure = CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), [FIRSTDATE] IN FILTERS ( 'Table 2'[Date] ) ) ) - CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), [LASTDATE] IN FILTERS ( 'Table 2'[Date] ) ) )If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,Thank you, Don Li. Unfortunately, that's not quite the result we're seeking, but it's close.
You're table below shows the correct schema - Employee ID, the employee's Arrive Date, and the employee's Depart Date. Over a period of time, we expect arrivals to increase along the Y-axis cumulatively, and the departures to decrease along the Y-axis cumulatively, so that at TODAY() or any days along the X-axis, we'd see the present-day employee count. Sorry if we hadn't made this clear at the outset.
- v-lid-msft
Hi FruitBat ,
Sorry for late reply, We can use the following measure to display the present-day employee count.
Measure = CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), NOT OR ( 'Table'[LASTDATE] < MIN ( 'Table 2'[Date] ), 'Table'[FIRSTDATE] > MAX ( 'Table 2'[Date] ) ) ) )
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,
- v-lid-msft
Hi FruitBat ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
