Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
My dataset has three columns: Employee ID, Start Date and End Date.
Based on this, we created two charts:
1)Headcount Onboard by Year: Shows the number of employees joining by month (count of employee id and start date).
2) Headcount Offboard by Year: Shows the number of employees leaving by month (count of employee id and end date).
I want to create a chart that shows the difference between the two by month (headcount variance). How do I do this?
Solved! Go to Solution.
Hi @Anonymous
You might refer to these similar threads for reference:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
https://community.powerbi.com/t5/Desktop/Count-of-Open-cases-over-time/td-p/761764
https://community.powerbi.com/t5/Desktop/Getting-a-running-total-of-current-open-Tickets/td-p/448431
Hi @Anonymous
You might refer to these similar threads for reference:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
https://community.powerbi.com/t5/Desktop/Count-of-Open-cases-over-time/td-p/761764
https://community.powerbi.com/t5/Desktop/Getting-a-running-total-of-current-open-Tickets/td-p/448431
@Anonymous , if both dates are on same table or different tables. The solution will have a small difference.
But refer to this blog or video.
I saw the message in the spanish channel already, so i will react here before its translated 😉
The measure would be a bit trickier to write, but i think it would be something like;
measure = CALCULATE(COUNT(Id), Startdate) - CALCULATE(Count(id),enddate)
Not exactly sure though, typing this from my phone. If you could provide me a sample dataset i will write the measure for you first thing in the morning. 🙂
Eric.
Thanks! Really appreciate it!
The sample dataset is below. I need a breakdown by month for my chart. Example - the breakdown for March 2020 should be -1 (2 people joining in March 2020 and 3 people leaving, so 2-3).
Here is the sample dataset:
EMPLOYEE ID | START DATE | END DATE |
846 | 12/31/2015 | 03/01/2020 |
3712 | 03/01/2015 | 03/31/2021 |
3700 | 03/26/2020 | 03/31/2021 |
867 | 03/02/2020 | 03/31/2021 |
4800 | 04/01/2019 | 03/01/2020 |
5691 | 01/04/2016 | 03/01/2020 |
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you Ashish! I am having trouble opening the file, but if it isn't too much trouble can you share the logic and the queries? Thanks!
Hi,
The file is downloading just fine. Please retry.
Thanks Ashish - I am having trouble opening because I am on an older version of Power BI. Unfortunately my organization has blocked the update for some reason. If it's not too much trouble, could you share the logic and queries. Thanks so much!
Hi,
There are actually quite many steps there so writing them here is not feasible.
Hey,
You can create a measure, something like:
Measure = SUM(OnboardByyear) - SUM(OffboardByYear)
With sample data i could test it and make sure it works. But this should work.
Goodluck!
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |