Difference in Count of Employees Joining vs. Leaving in a month

[Anonymous]

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?

[v-diye-msft · Accepted 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 

https://community.powerbi.com/t5/Desktop/Opened-and-Closed-ticket-count-by-Shift-along-with-Carry-Over/td-p/620421 

[amitchandak]

@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.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://youtu.be/e6Y-l_JtCq4

[EricHulshof]

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. 

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

 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!

[Ashish_Mathur]

Hi,

The file is downloading just fine.  Please retry.

[Anonymous]

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!

[Ashish_Mathur]

Hi,

There are actually quite many steps there so writing them here is not feasible.

[EricHulshof]

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!