Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SergioGG86
Frequent Visitor

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_HIST_LABORAL.FECHA_MOV: Movement Date
Movimiento: Movement type
FECHA_BAJA: End Date

The field RH_HIST_LABORAL.FECHA_MOV indicates which date a change by the HR excecutive was made.

In Movimiento "ALTA" means a person (ID) was hired, "REINGRESO" means a person was rehired and gets his last same ID.

In movimiento anything that contains BAJA means this person stopped working.

We need to know the daily, monthly & yearly head count, I attached the example.

https://drive.google.com/drive/folders/19Gystr2uB1tIWy0mpLM3LFuhTeVte0-3?usp=sharing 

6 REPLIES 6
lbendlin
Super User
Super User

So basically for each employee and day in your dates table you need to calculate if that employee was employed on that day or not.

 

Employed means the ALTA or REINGRESO  events are the last event in the past or today. If the BAJA/BAJO event is the last one today or in the past then the employee has left.

 

We also need to ignore all the other events .

 

This is what I have so far. 

 

 

employed = 
var emp=max(Table[ID])
var d=max(Calendar[date])
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"})

 

 

I think these are ok steps but it starts to get thin. Maybe someone else can step in.

 

ok, 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?

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.

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors