Forum Discussion
Calculate Current Employee Count based on Joined and Leave Date
Hi,
So I have three columns:
- Joining (formula that calculates how many joiners by date)
- Leaving (formula that calculates how many leavers by date)
- Colleagues (Where i'm trying to get the employee headcount).
I have a sample of the data below:
YearMonthJoiningLeaving Colleagues 14 0 14 2015-05 0 0 14 2015-06 0 0 14 2015-07 1 0 14 2015-08 1 0 14 2015-09 0 0 14 2015-10 0 0 14 2015-11 0 0 14 2015-12 0 0 14 2016-01 0 0 14 2016-02 0 0 14 2016-03 1 0 14 2016-04 0 1 16 2016-05 0 1 15 2016-06 1 0 14 2016-07 1 1 15 2016-08 1 0 14 2016-09 0 0 14
The formula for Colleagues is:
Colleagues= CALCULATE([Joining],FILTER(ALL(DateD2[Date]),DateD2[Date]<=MAX(DateD2[Date]))) - CALCULATE([Leaving],FILTER(ALL(DateD2[Date]),DateD2[Date]<=MAX(DateD2[Date])))
the DateD2 is a date dimenson which is where the YearMonth column is pulling from.
Essentially I want this to show me the current headcount by month. As you can see though in 2015-07 and 2015-08 there have been 2 joiners but this hasn't been affected the Colleagues column. Only when there has been a leaver does this figure change.
My Joining formula have used the USERELATIONSHIP function to get the relevant figures (as it is based on a flat excel file showing joining date and leaving date) so i'm not sure if this is affected? I'm hoping i can amend my above formula though to get 2015-07 to show 15, 2015-08 to show 16 etc.
Hope this makes sense!
Robert
Anonymous
I tried to build the data model with the data you provided. The measures seemed to work well as below. I’ve uploaded my .pbix file here for reference. Could you please take a look at it to see if there is any difference?
Best Regards,
Herbert
6 Replies
v-haibl-msftMicrosoft Employee
Anonymous
So both of Joining and Leaving are Measures, right? I try to build a simple data model and it seems that your Colleagues measure worked well as below.
I’m curious to see a sample of your flat excel table which shows joining date and leaving date, and the measure formula of Joining and Leaving.
BTW, have you created relationship between DateD2 and flat excel table with the date key?
Best Regards,
Herbert
Hi - yes both are measures. I've posted a sample of my data below:
Forename Joining Date Leaving Date John 28/02/2005 Linda 04/02/2016 Patricia 29/03/2004 Jennifer 22/02/2016 08/07/2016 Jennifer 29/07/2016 Stephen 02/01/2007 Alexander 19/08/2013 02/10/2015 Christopher 10/11/2015 01/03/2016 Graham 02/06/1997 08/05/2015 Neil 30/06/1975 31/12/2015 Stephen 24/02/2015 18/09/2015 Mwenda 24/02/2015 Maureen 23/02/2004 George 22/11/2004 26/06/2015
The formulas for both measures are quiet long-winded (and i'm willing to accept there may be an easier way!) but here it goes:
Joining =
Joining = IF( CALCULATE( COUNT( EmployeeDB[JoiningDate]) ,USERELATIONSHIP(DateD2[Date],EmployeeDB[Joining Date]), ALL(EmployeeDB[Leaving Reason]))=BLANK(),0, CALCULATE(COUNT(EmployeeDB[Joining Date]), USERELATIONSHIP(DateD2[Date],EmployeeDB[Joining Date]), ALL(EmployeeDB[Leaving Reason])))
Leaving =
Leaving = IF( COUNT(EmployeeDB[Leaving Date])=BLANK(),0, COUNT(EmployeeDB[Leaving Date]))
Yes - active relationship is between Leaving Date and DateD2 table, with the USERELATIONSHIP function above reversing this for the Joining formula.
Thanks,
Robert
- v-haibl-msft
Anonymous
I tried to build the data model with the data you provided. The measures seemed to work well as below. I’ve uploaded my .pbix file here for reference. Could you please take a look at it to see if there is any difference?
Best Regards,
Herbert