Forum Discussion
Calculate Current Employee Count based on Joined and Leave Date
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
Microsoft EmployeeAnonymous
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
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 :)
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.
This was really helpful to me thank you :-)
will this still work if i filter out some reason codes please?
Thank you
Loren