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.