Calculate Current Employee Count based on Joined and Leave Date

[robstewart89]

Hi,

So I have three columns:

1. Joining (formula that calculates how many joiners by date) 
2. Leaving (formula that calculates how many leavers by date)
3. 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

[v-haibl-msft]

@robstewart89

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

[robstewart89]

@v-haibl-msft

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 · Accepted solution]

@robstewart89

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

[Anonymous]

Hello @v-haibl-msft,

Your answer made me understand a lot of thing in PBI ! Thanks a lot !

Still, I struggled a lot with the leaving data on my dataset. There were wrong compared to manual Excel filtering count.

I found why and thought I would share to help someone like me around 🙂

Per example, for 2016, I only get leaving people of 2016 when they arrived also in 2016! I was missing all other leaves.

I then change the "leaving" measure into a similar code as "joining" which was giving correct numbers.

I used the relation of Date<=>EmployeeDB[Leaving Date] instead of "Arrival". that make much sense and is probably root of wrong calculation.

Leaving = 
IF (
    CALCULATE (
        COUNT ( EmployeeDB[Leaving Date] );
        USERELATIONSHIP ( DateD2[Date]; EmployeeDB[Leaving Date] );
        ALL ( EmployeeDB[Leaving Reason] )
    )
        = BLANK ();
    0;
    CALCULATE (
        COUNT ( EmployeeDB[Leaving Date] );
        USERELATIONSHIP ( DateD2[Date]; EmployeeDB[Leaving Date] );
        ALL ( EmployeeDB[Leaving Reason] )
    )
)

Hope that will help someone someday.

Tim Mayle.

[Anonymous]

This was really helpful to me thank you 🙂

will this still work if i filter out some reason codes please?

Thank you

Loren

[robstewart89]

@v-haibl-msft

Hi -thanks for sending over. I had the formulas set up the exact same way but realised my error!

Essentially I had tried and tested lots of other options before this, one was to have a second Leaving Date column (LeavingDate2) which was an IF formula saying if there was no leaving date then provide 01/January/2099. For some reason I had kept this measure and used that in my Relationship with the DateD2 table instead of the normal Leaving Date column. 

Changing this has helped show what I need.

Thanks for your help 🙂