Calculate running total with two date columns in the Table

[bourne2000]

Hi

I have a below table

Employee Number	Hired Date	Termination Date	Status
332	25/10/1994	Monday, 1 August 2016	Terminated
339	4/01/1995	Monday, 1 August 2016	Terminated
604	18/08/1998	Monday, 1 August 2016	Terminated
624	24/11/1998	Monday, 24 April 2017	Terminated
660	17/08/1999	Friday, 16 June 2017	Terminated
668	14/09/1999	Monday, 6 August 2018	Terminated
778	26/11/2001	Thursday, 13 September 2018	Terminated
792	13/02/2002	Tuesday, 9 October 2018	Terminated
797	11/03/2002	Monday, 5 November 2018	Terminated
818	4/09/2001	Monday, 4 March 2019	Terminated
819	4/09/2001	Monday, 27 May 2019	Terminated
831	29/07/2002	Sunday, 16 January 2022	Terminated
853	5/09/2002	Monday, 26 August 2019	Terminated
969	17/10/2003		Active
970	29/10/2003		Active
1004	21/04/2004		Active
1019	20/08/2004		Active
1039	27/09/2004		Active
1083	13/08/2005		Active
1093	25/08/2005		Active
1148	26/09/2005		Active
1157	4/11/2005		Active
1186	21/08/2006		Active

I want to calculate Running total for Active Employees and Terminated Employees. I calculated total active and total termination employees using below dax measures

Total Active = CALCULATE(COUNT('Table'[Employee Number]),'Table'[Status] = "Active")

Total Termination = CALCULATE(COUNT('Table'[Status]),'Table'[Status] = "Terminated")

I created a Date Table and connected it with the Hire date in the table. 

Then calculated running total for active employees as below

Active Employee RunningTotal = var MaxDate = MAX('Date'[Date])

RETURN

CALCULATE([Total Active],'Date'[Date] <= MaxDate,
ALL('Date'[Date]))

However, I am not able to calculate the running total for Terminated employees. Finally, I need to take the difference between active and running employees running total.

Can anyone advise how to do the above with two date columns in the table?

[FarhanAhmed · Accepted solution]

You need to create an inactive relationship between your calendar date and terminated date.

and improve the DAX measure as 

Total Termination = CALCULATE(COUNT('Table'[Status]),'Table'[Status] = "Terminated",USERELATIONSHIP(Calendar[Date],Table[TerminationDate]))