Forum Discussion

SergioGG86's avatar
SergioGG86
Frequent Visitor
6 years ago

Impossible Head Count

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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

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