Cálculos sobre numero de empleados a fecha determinada cuando tienen fecha de inicio y fin de contrato.

Me ha llegado esta consulta por linkedin de Javier San Juan que con su permiso y considerándolo un tema interesante voy a contestarla con este post en el blog.

Consulta:

Tengo una tabla de empleados con sus fechas de alta y baja en la empresa (si siguen en activo en la empresa la fecha de baja está en blanco). ¿Existe posibilidad en DAX de crear alguna medida que calcule el nº de empleados a una determinada fecha -vinculada a una tabla calendario para poder seleccionar cualquier fecha, mes o año y que me indique cuántos empleados había en ese momento- (No me gustaría tener que calcular una medida para cada año ni una columna calculada por cada año calculando si ese empleado está activo en ese determinado año? Esto me obligaría a hacer nuevos cálculos cada nuevo año y me gustaría dejarlo preparado para simplemente actualizar los datos). También me gustaría calcular por año el dato de la plantilla media. Es decir, si un empleado se incorporó el 01/04/2012 y causó baja el 30/06/2019, que en el año 2012 compute como 0,75, los años 2013 a 2018 como 1 y el año 2019 como 0,5.

Este tipo de cálculos son muy comunes y necesarios en contextos como el de recursos humanos, donde podemos tener informes sobre el número de trabajadores contratados, o los períodos de ausencias de los trabajadores, por poner un ejemplo. Vamos a poner un ejemplo de las técnicas que utilizamos para los diferentes cálculos.

1º Vamos a crear una pequeña tabla de hechos de RRHH con algunos movimientos de trabajadores. Vamos a trabajar con pocas líneas en el ejemplo, pero estos cálculos son extrapolables por supuesto a empresas con mucho volumen de movimientos de personal.

let

Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(«ldLNCsIwDMDxd+lZaJJ9tH0E796GB2EKgm7i+x/sVsF2TVoHuwR+lOzfDoNCdVDHabze7tN9nP0AqP1HgMYP6nwYFNVJUydtnXR10m8JdtomwlSF3QpqNUJCXJ0g/GGyug1oTP8Is7yMyfoyJgvMmKXw6fp8ze/L49u3X4QNHFwYVtvvsGaHtQVLVgMtgwvWFay/1/A4giX4fwfCHZZK+/rBRP8m9XXxua7cl7NSX85KfTkr9WWs2JezUl/OSn0522Q2vPD1LiB9D21uKbbRO6P83lKLxR2gjfft5R38oantijv8LBoN9mvPHw==», BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IdTrabajador = _t, TipoContrato = _t, FechaAlta = _t, FechaBaja = _t]),

#»Tipo cambiado» = Table.TransformColumnTypes(Origen,{{«IdTrabajador», Int64.Type}, {«TipoContrato», type text}, {«FechaAlta», type date}, {«FechaBaja», type date}}),

#»Valor reemplazado» = Table.ReplaceValue(#»Tipo cambiado»,null,PrincipioAñoSiguiente,Replacer.ReplaceValue,{«FechaBaja»})

in

#»Valor reemplazado»

Este código nos dará en principio un error ya que no encuentra el parámetro PrincipioAñoSiguiente

Creamos otra consulta nueva que renombramos como PrincipioAñoSiguiente con el siguiente código:

let

Origen = DateTime.LocalNow(),

#»Convertida en tabla» = #table(1, {{Origen}}),

#»Tipo cambiado» = Table.TransformColumnTypes(#»Convertida en tabla»,{{«Column1″, type date}}),

#»Final del año calculado» = Table.TransformColumns(#»Tipo cambiado»,{{«Column1″, Date.EndOfYear, type date}}),

#»Personalizada agregada» = Table.AddColumn(#»Final del año calculado», «FinalContratoFicticio», each Date.AddDays([Column1],1), type date),

#»Columnas quitadas» = Table.RemoveColumns(#»Personalizada agregada»,{«Column1»}),

FinalContratoFicticio = #»Columnas quitadas»{0}[FinalContratoFicticio]

in

FinalContratoFicticio

Este parámetro es dinámico y el 1/1/2020 cambiaría y pasaría a ser 01/01/2021

Con el ajuste que hacemos de sustituir el valor nulo de la fecha de baja por el primer día del año siguiente al actual estamos dando una fecha “ficticia” de baja que siempre será futura y del año siguiente para que de esa manera los contratos sin fecha de baja nos los contabilice siempre como vigente y no nos aparezca como baja en el ejercicio actual

Ya fuera del editor de M, vamos a crear en DAX una tabla de fechas simple:

Con CALENDARAUTO (12) tendríamos nuestra tabla de fechas, pero vamos a crearle el Ejercicio y el mes. A mi personalmente me gusta hacerlo en el propio código de creación de la tabla sin tener que crear columnas calculadas:

dFechas = var Fechas=CALENDARAUTO(12)

return

SELECTCOLUMNS(Fechas;

«Fecha»; [Date];

«Ejercicio»; YEAR([Date]);

«MesNro»; MONTH([Date]);

«EjercicioMes»; FORMAT([Date];»YYYY-MM»))

Y por último para completar nuestro minimodelo, vamos a establecer las relaciones, ambas inactivas entre FechaAlta y Fecha y entre FechaBaja y Fecha.

Esto es muy importante porque de forma contraria no funcionarían los cálculos que voy a proponer, ambas relaciones deben estar inactivas.

A partir de aquí ya tenemos nuestro pequeño modelo listo para que nos sirva de ejemplo y empezar a establecer medidas

Nº Altas = CALCULATE(COUNTROWS(FactRRHH);USERELATIONSHIP(dFechas[Fecha];factRRHH[FechaAlta])

Como cada línea de nuestra tabla es un contrato de trabajo, el numero de altas es muy sencillo de calcular, calculamos el nº de líneas usando la relación inactiva correspondiente.

El número de bajas por otro lado también es simple, contar las líneas igualmente, pero teniendo en cuenta la otra relación inactiva

Nº Bajas = CALCULATE(COUNTROWS(FactRRHH);USERELATIONSHIP(dFechas[Fecha];factRRHH[FechaBaja])

En nuestro ejemplo nos daría estos datos por ejercicio y mensualizados:

El siguiente cálculo que vamos a hacer es el del nº de trabajadores a fecha, es decir, en una fecha dada, cuantos trabajadores había en ese momento dados de alta y esto lo vamos a hacer con una fecha de referencia que será siempre la última fecha del contexto.

Nº Trabajadores a Fecha = var FechaComparacion=LASTDATE(dFechas[Fecha])

return

CALCULATE(COUNTROWS(FactRRHH);FILTER(FactRRHH;FactRRHH[FechaAlta]<=FechaComparacion);FILTER(FactRRHH;FactRRHH[FechaBaja]>=FechaComparacion))

Cuando ponemos este cálculo en un gráfico de áreas por fecha, este es el resultado que nos arroja:

El último cálculo de la consulta era obtener las medias de trabajadores en un contexto determinado. Como el cálculo Nº trabajadores a fecha nos indica el nº de trabajadores existentes para cada día de nuestra tabla de fechas, con la función AVERAGEX podemos obtener la media deseada haciendo el cálculo sobre la dimensión fecha.

Media trabajadores = AVERAGEX(dFechas;[Nº Trabajadores a Fecha])

Lo que hace este cálculo es sumar el número de trabajadores que están activos cada día de la tabla de calendario y dividirlo entre el número de días del contexto en el que nos encontremos.

Cuatro simples cálculos que espero que contesten a nuestro amigo Javier y os sirvan de ayuda.

Importantísimo y clave del cálculo de los trabajadores activos a fecha es que las dos relaciones estén inactivas.

Os dejo el enlace de descarga del pbix utilizado en el ejemplo