Headcount for Terminated Employees

[Anonymous]

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

[adeeln]

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

[Anonymous]

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