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 c...
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