Calculo de trabajadores en periodo

Una vez tenemos definida nuestra fecha de inicio, debemos definir nuestra fecha fin, ya que no tiene ningún sentido crear una dimensión tiempo de 1000 años, ya que lo único que nos va a aportar es tamaño a nuestro modelo. ¿No creéis? ¿Y hasta dónde calcumos la fecha fin de nuestra dimensión tiempo? Hasta el final del año en curso, de esta manera conseguimos que sea dinámica. Para eso creamos una consulta en blanco:

En el Editor que se nos ha desplegado, tenemos que obtener el año en curso, para ello usamos la función Date.Year para la función DateTime.LocalNow():

let

// Extraigo el año del sistema

Año = Date.Year(DateTime.LocalNow()),

¿Y cómo calculamos el final del año? Pues concatenando al año calculado 31/12 ya que por ahora todos los años terminan siempre en la misma fecha J Para eso usamos la función Text.From:

Fecha = Text.From("31/12/") & Text.From(Año),

Y ahora ya tenemos la fecha fin de la dimensión tiempo pero en formato texto, por lo que la convertimos en tipo fecha con la función Date.FromText:

FechaFin = Date.FromText(Fecha)

Y nos queda un código como el siguiente:

 

Ahora ya podemos calcular nuestra dimensión tiempo, para ello lo primero que tenemos que hacer es calcular el número de días que hay entre la fecha de inicio y la fecha fin que tenemos. Para ello usamos la función Duration.Days. (le sumamos 1 ya que sólo tiene en cuenta una de las fechas del intervalo):

// Calculamos el intervalo de días

DiasIntervalo = Duration.Days( FechaFin - FechaInicio) +1 ,

Una vez tenemos el número de días que tenemos entre fechas, tenemos que listarlos.  Para ello, usamos la función List.Dates:

// listamos las fechas del intervalo

Fecha = List.Dates(FechaInicio,DiasIntervalo, #duration(1,0,0,0)),

A continuación, lo convertimos en lista mediante la función Table.FromList, que nos la genera automáticamente la interfaz de usuario:

//  Convertimos en tabla la lista

  #"Convertido en tabla" = Table.FromList(Fecha, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Ahora renombramos la columna 1 por Fecha:

// Cambiamos el nombre de la columna 1

  #"Columnas con nombre cambiado" = Table.RenameColumns(#"Convertido en tabla", {{"Column1", "Fecha"}}),

Y le cambiamos el tipo a la columna para que sea tipo Fecha:

// Cambiamos el tipo de la columna

    #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Columnas con nombre cambiado", {{"Fecha", type date}}),

 

Para crear una dimensión tiempo completa, insisto, te copio aquí el link donde lo explico.

Una vez creada la dimensión tiempo, podemos pasar a la tabla de hechos y analizarla. Vemos que la columna FechaFin viene con valores null si no existe fecha fin de contrato en cuestión:

 

¿Y qué hacemos con esos valores? Pues un truquito que nos va solucionar muchos problemas. Los vamos a sustituir esos valores por una fecha futura, por ejemplo, el final del año próximo o lo que es lo mismo el 31/12/2024 y tenemos que hacer que este campo sea dinámico de manera que no lo tengamos que modificar todos los años. ¿Y cómo lo hacemos? Pues de una manera muy similar a cómo hemos obtenido la FechaFin pero con la salvedad que en el primer paso cuando calculamos el año actual le sumamos 1:

Y ahora ya tenemos preparado nuestro modelo para calcular los empleados activos en el período seleccionado.

Nota: Para este ejemplo no lo he hecho ya que es ilustrativo, pero deberíamos generar nuestras propias claves subrogadas.

Una vez, ya hemos hecho nuestro tratamiento de los datos, ahora ya podemos proceder a generar nuestras relaciones entre nuestras dimensiones y nuestra tabla de hechos.

 

Vamos a calcular el número de altas que se ha realizado en la empresa, para eso creamos la siguiente métrica:

Altas =

CALCULATE (

    COUNTROWS ( FactContratos ),

    USERELATIONSHIP ( DimFecha[Fecha], FactContratos[FechaInicio] )

)

Lo que realizamos es un conteo del número de filas de la tabla FactContratos y use la relación entre la tabla Fecha y la columna de FechaInicio.

Y como se puede ver, hemos tenido 60 contratos, pero de los cuales 50 han sido a trabajadores diferentes, o lo que es lo mismo, a 10 trabajadores se les ha renovado el contrato en algún momento.

Ahora vamos a calcular en número de bajas que ha tenido la empresa, es decir, el número de contratos que se han finalizado en algún momento. Para ello generamos la siguiente métrica:

Bajas en periodo =

VAR UltimaFecha =

    LASTDATE ( DimFecha[Fecha] )

Return

CALCULATE (

    COUNTROWS ( FactContratos ),

    FactContratos[FechaFin]<= UltimaFecha,

    USERELATIONSHIP ( DimFecha[Fecha], FactContratos[FechaFin] )

)

 Aquí creamos una variable con el valor de la última fecha seleccionada, y contamos el número de filas de la tabla de hechos en los que la columna FechaFin sea menor o igual a la fecha seleccionada y que la relación con nuestra tabla de hechos sea mediante la columna FechaFin.

 

Pero aquí estamos en el mismo punto que con las altas. Aquí pueden existir trabajadores que se les haya finalizado 2 o más veces el contrato, por lo que si queremos obtener el número real de trabajadores que han causado baja realizamos la siguiente métrica:

Bajas únicas en periodo =

 

VAR UltimaFecha =

    LASTDATE ( DimFecha[Fecha] )

Return

CALCULATE (

    DISTINCTCOUNT(FactContratos[IdTrabajador]),

    FactContratos[FechaFin]<= UltimaFecha,

    USERELATIONSHIP ( DimFecha[Fecha], FactContratos[FechaFin] )

)

Lo mismo para esta métrica que para las altas únicas, realizamos un DISTINCTCOUNT sobre la columna IdTrabajador.

 

 

 Y ahora vamos a calcular el número de trabajadores activos en la empresa. Para ello, creamos la siguiente métrica:

Nº Trabajadores activos =

 

VAR UltimaFecha =

    LASTDATE ( DimFecha[Fecha] )

RETURN

    CALCULATE (

        DISTINCTCOUNT( FactContratos[IdTrabajador] ),

        FILTER ( FactContratos, ( FactContratos[FechaInicio] <= UltimaFecha ) ),

        FILTER ( FactContratos, ( FactContratos[FechaFin] >= UltimaFecha ) )

    )

 

 ¿Qué estamos haciendo en esta métrica? Estamos contando el número único del IdTrabajador de la tabla de hechos FactContratos y como argumento de filtro de la función CALCULATE, introducimos el iterador FILTER recorremos la tabla FactContratos en las que la FechaInicio sea menor o igual a la variable UltimaFecha. E introducimos un segundo argumento de filtro para la función CALCULATE (recordad que se ejecutan como un AND lógico), el segundo argumento es otro  FILTER para recorrer la tabla FactContratos en las que la FechaFin sea mayor o igual a la variable UltimaFecha. ¿Y por qué mayor a UltimaFecha? Por qué acordaros que en Power Query hemos calculado una fecha ficticia futura y la hemos introducido en la tabla de hechos en la columna FechaFin cuyos valores eran null, de esta manera siempre los contratos activos van a tener una fecha futura. Esta medida la llevamos a una gráfico y como resultado obtenemos:

Y podemos ver que la empresa ha tenido altibajos en su masa de personal y tenemos como resultado nuestro informe con nuestros trabajadores activos:

En el que podemos ver que hemos tenido 60 nuevas altas en el periodo seleccionado de las cuales 50 trabajadores han sido únicos, es decir, a 10 trabajadores se les ha prorrogado el contrato en el algún momento. A la vez la empresa ha tenido 17 bajas, de las cuales 1 de ellas ha sido repetida, o lo que es lo mismo a un trabajador se le ha prorrogado un contrato y uno de los siguientes no se ha prorrogado, por tanto, tenemos 43 personas en activo en este momento.