Headcount for Terminated Employees

Question

Hello,&nbsp;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.&nbsp;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() 
&amp;&amp; 'Emp Data'[Job Termination Date] &lt;= currentDate &amp;&amp; 'Emp Data'[Job Rehire Date] &lt; 'Emp Data'[Job Termination Date] &amp;&amp; 'Emp Data'[Job Termination Date] &gt;= MIN('Calendar 2'[Date])
)))&nbsp;Any help appreciated!Sample Data below:&nbsp;Employee IDEmployee StatusHire DateJob Rehire DateService DateJob Termination Date1000000Terminated01/09/2017&nbsp;01/09/201706/28/20191000002Active08/22/2016&nbsp;08/22/2016&nbsp;1000004Active04/10/2017&nbsp;04/10/2017&nbsp;1000006Active10/12/2015&nbsp;10/12/2012&nbsp;1000008Active08/14/2002&nbsp;08/14/2002&nbsp;1000010Active05/16/2011&nbsp;05/16/2011&nbsp;1000012Active04/27/2009&nbsp;04/27/2008&nbsp;1000013Terminated05/31/2016&nbsp;05/31/201607/05/20191000014Terminated04/24/2017&nbsp;04/24/201712/06/20181000015Active01/28/2008&nbsp;01/28/2008&nbsp;1000018Terminated10/20/2014&nbsp;10/20/201410/19/20181000019Active10/16/2017&nbsp;10/16/2013&nbsp;1000022Terminated08/31/2015&nbsp;11/27/201706/28/20191000023Active11/23/2015&nbsp;11/23/2015&nbsp;1000025Terminated01/17/201501/22/201801/17/201505/31/20181000027Active04/10/2017&nbsp;04/10/2017&nbsp;1000028Terminated05/09/2005&nbsp;05/09/200507/12/2019

anonymous · Answer

We need to see:&nbsp;1. The full model.2. A CLEAR description of the procedure that is supposed to produce the correct number for any selection of attributes.&nbsp;Then and only then can we work on this.&nbsp;BestDarek

anonymous · Answer

Create an inactive relationship of your date dimension with the termination datethen use this measure&nbsp;Termination =
CALCULATE (
    COUNT ( FactTable[TerminationDate] ),
    USERELATIONSHIP ( DimDate[Date], FactTable[TerminationDate] )
) + 0&nbsp;&nbsp;

Forum Discussion

Headcount for Terminated Employees

2 Replies

Related content

Find Terminated Employees

Headcount, Joiners, leavers and Mobility of employees.

Employee Headcount

Employee Headcount history changes

Active Employees HR headcount dashboard

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