Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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,
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.
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,
Just check if this can help
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |