Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I am fairly new to Power BI and I am trying to create a measure that will produce a headcount for terminated employees. I am using Pivots in Excel to check the accuracy of the measure and it's not producing the correct number.
Please note: Not all employees with termination dates are necessarily terminated because we people that are rehired.
This is the current measure I am using:
Terminated Employee Count = VAR currentDate = MAX( 'Calendar 2'[Date]) return CALCULATE( COUNTROWS('Emp Data' ), FILTER('Emp Data',( ISBLANK('Emp Data'[Job Termination Date]) = FALSE() && 'Emp Data'[Job Termination Date] <= currentDate && 'Emp Data'[Job Rehire Date] < 'Emp Data'[Job Termination Date] && 'Emp Data'[Job Termination Date] >= MIN('Calendar 2'[Date]) )))
Any help appreciated!
Sample Data below:
Employee ID | Employee Status | Hire Date | Job Rehire Date | Service Date | Job Termination Date |
1000000 | Terminated | 01/09/2017 | 01/09/2017 | 06/28/2019 | |
1000002 | Active | 08/22/2016 | 08/22/2016 | ||
1000004 | Active | 04/10/2017 | 04/10/2017 | ||
1000006 | Active | 10/12/2015 | 10/12/2012 | ||
1000008 | Active | 08/14/2002 | 08/14/2002 | ||
1000010 | Active | 05/16/2011 | 05/16/2011 | ||
1000012 | Active | 04/27/2009 | 04/27/2008 | ||
1000013 | Terminated | 05/31/2016 | 05/31/2016 | 07/05/2019 | |
1000014 | Terminated | 04/24/2017 | 04/24/2017 | 12/06/2018 | |
1000015 | Active | 01/28/2008 | 01/28/2008 | ||
1000018 | Terminated | 10/20/2014 | 10/20/2014 | 10/19/2018 | |
1000019 | Active | 10/16/2017 | 10/16/2013 | ||
1000022 | Terminated | 08/31/2015 | 11/27/2017 | 06/28/2019 | |
1000023 | Active | 11/23/2015 | 11/23/2015 | ||
1000025 | Terminated | 01/17/2015 | 01/22/2018 | 01/17/2015 | 05/31/2018 |
1000027 | Active | 04/10/2017 | 04/10/2017 | ||
1000028 | Terminated | 05/09/2005 | 05/09/2005 | 07/12/2019 |
Create an inactive relationship of your date dimension with the termination date
then use this measure
Termination =
CALCULATE (
COUNT ( FactTable[TerminationDate] ),
USERELATIONSHIP ( DimDate[Date], FactTable[TerminationDate] )
) + 0
We need to see:
1. The full model.
2. A CLEAR description of the procedure that is supposed to produce the correct number for any selection of attributes.
Then and only then can we work on this.
Best
Darek
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |