Forum Discussion
HeadCount by type
Hi Everyone, I need to calculate the head count on this database. In the table RH Total, there are 3 fields were going to use: ID, RH_HIST_LABORAL.FECHA_MOV, Movimiento. ID: Employee ID RH_HI...
lbendlin
Super User
Super Userok, getting a bit closer. Still not sure how to do the final count.
employed =
var d=max(Calendar[date])
var emp=CALCULATE(LASTNONBLANK(Table[ID],1),CROSSFILTER(Calendar[date],Table[RH_HIST_LABORAL.FECHA_MOV],None))
var lastd=CALCULATE(max(Table[RH_HIST_LABORAL.FECHA_MOV]),ALL(Table),Table[ID]=emp,Table[RH_HIST_LABORAL.FECHA_MOV]<=d,Table[Movimiento] in {"ALTA","BAJA POR CAMBIO DE EMPRESA","BAJO POR RENUNCIA VOLUNTARIA","REINGRESO"})
var lastev=CALCULATE(max(Table[Movimiento]),All(table),Table[ID]=emp,Table[RH_HIST_LABORAL.FECHA_MOV]=lastd)
return left(lastev,4)
you need to have an active relationship between the table date and the employee table?
- lbendlin
yes, as we use a CROSSFILTER(none) which basically multiplies the employee list with the dates table. You may also need the relationship in other parts of your report.
- lbendlin
Quote: "We need to know the daily, monthly & yearly head count" - For monthly and yearly, do you mean at the end of the month or year? For employee 13491 you would then also miss all the events that happen in May 2016.