Impossible Head Count

SergioGG86 · ‎07-07-2020

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 · ‎07-18-2020

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

Anonymous · ‎07-17-2020

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