Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
So I have three columns:
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
Solved! Go to Solution.
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
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
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
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
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |