Impossible Head Count

[SergioGG86]

Example File I have the following table, im trying to get a Head Count till today.         

Area: the area where the employee is currently working.

ChangeDate: It records the date an employee was changed in area, hired, rehired or fired.

Movement: Records what movement was made (Salary rise, quitted, Hired or Rehired)

ID: Employee ID

Movement	ChangeDate	Movement	ID
ADC	03/04/1990	Hired	10000000014
ADC	19/05/2018	Quitted	10000000018
ADC	16/02/1998	Hired	10000000018
ADC	08/11/2011	Hired	10000000024
ADC	01/11/2005	Hired	10000000044
ADC	08/05/1997	Hired	10000000054
Green Building	29/04/2003	Hired	10000000071
Training	06/08/2012	Hired	10000000112
Creative	23/02/1998	Hired	10000000119
ADC	18/01/1999	Hired	10000000191
ADC	28/01/1999	Hired	10000000200
ADC	23/04/1998	Hired	10000000232
ADC	29/06/1998	Hired	10000000234
ADC	17/05/1999	Hired	10000000271
Environmental Compliance	20/05/2016	Quitted	10000000288
Environmental Compliance	10/06/1999	Hired	10000000288
ADC	10/06/1999	Hired	10000000293

Im currently using:

HEADCOUNT:=CALCULATE(DISTINCTCOUNT([ID]),

FILTER(VALUES('RH Total'[ChangeDate]),'RH Total'[ChangeDate]<=MAX('Calendar'[Date])),

FILTER(VALUES('RH Total'[ChangeDate]),'RH Total'[ChangeDate]>=MIN('Calendar'[Date])))

This formula is not working because it is counting me the persons that were rehired and getting the first entry date.

Note:

• An employee can be rehired with the same ID he had the first time he was hired.
• We should later on filter by area.
• We should filter by date (lets say April 1st) and get all the ids that were active between dates.

[lbendlin]

Why not continue to use your prior solution with CROSSFILTER (...,...,none) ?

[Anonymous]

Hi @SergioGG86 

Assuming that employee is first Quitted before the employee can be rehired.

Something like this if you have a relationship between your dates table, slicer and movement table.

HC = 
var _Hired = CALCULATE(COUNTROWS('Table'), 'Table'[Movement] = "Hired")
var _Quitted = CALCULATE(COUNTROWS('Table'), 'Table'[Movement] = "Quitted")

var result = _Hired - _Quitted

return result

or if there is no relationship:

HC_Between = 

var _FromDate = Min(Dates[Date])
var _ToDate = Max(Dates[Date])

var _Hired = CALCULATE(COUNTROWS('Table'), 'Table'[ChangeDate] >= _FromDate,'Table'[ChangeDate] <= _ToDate, 'Table'[Movement] = "Hired")
var _Quitted = CALCULATE(COUNTROWS('Table'), 'Table'[ChangeDate] >= _FromDate,'Table'[ChangeDate] <= _ToDate, 'Table'[Movement] = "Quitted")

var result = _Hired - _Quitted

return result

Jan