Tratamiento de Días No Laborales, Parte II.
Este artículo pertenece a la segunda parte de la serie de cuatro que se dedican a ver como trabajar y realizar cálculos con días laborales y no laborales con Power BI.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. Relacionar Tablas
2. Columna de Días Laborales en la Tabla de Calendario
a. Fines de Semana (Operador IN)
b. Días Festivos (Función RELATED)
c. Consolidado: Días Laborales
2. Medida para Ingresos por Día Laboral
a. Caso Simple (Función DIVIDE)
b. El problema del total de columnas (Función FILTER)
c. El problema del total General (Función CROSSJOIN)
3. Otros Periodos de Tiempo (Semestres y Trimestres)
Bienvenido a esta nueva entrega donde estamos indagando un poco acerca de cómo trabajar con días laborales y no laborales en Power BI
Si no has leído la primera parte de esta serie, te recomiendo muchísimo que le des una oportunidad, no porque sea un requisito obligatorio, porque la verdad, si ya tienes una tabla que te liste todos los días no laborales (porque de eso se trata el primer artículo), entonces, puedes proceder con las medidas del presente post.
A pesar de ello, es altamente recomendable por los trucos de Power Query que allí residen, unas utilidades que es bueno que tengas en tu kit de herramientas.
Relacionar Tablas
En todo modelo de datos, por más simple que sea, debe existir una tabla de calendario (también conocida como tabla de fechas)
Por lo tanto, nuestra tabla con los días no laborales (Fechas Festivas) la vamos a relacionar con nuestra tabla de Calendario
El procedimiento de establecer la relación entre un par de tablas es bien sencillo, para nuestra caso: Arrastrar del campo Fechas de la tabla de Calendario a el campo Fechas de Festividad de la tabla FechasFestivas
Columnas de Días Laborales en Tabla de Calendario
En cada fila vamos de la tabla de Calendario debemos indicar si la fecha corresponde a un día laboral o no laboral.
Optar por utilizar TRUE y FALSE en la identificación es una vía válida, sin embargo, una mejor alternativa es indicar el número 1 para día laboral y el 0 para día no laboral, así se nos facilitará la medida de «conteo» más adelante.
Empezamos con →
Fines de Semana (Operador IN)
Creamos una nueva Columna Calculada, donde lo primero que vamos a realizar es identificar si el día corresponde a un día del fin de semana (sábado o domingo). Eso lo conseguimos simplemente comprobando si la columna DS Número es 6 o 7, con lo cual nos vamos a apoyar del operador IN para comprobar en la lista.
Adicionalmente, vamos a utilizar variables a razón de que en la columna se necesitan varios pasos, por un lado, fines de semana y en otro paso los días festivos; habiendo dicho esto, la identificación del fin semana es posible crearla de la siguiente manera:
Día Laboral =
VAR EsFinDeSemana = Calendario[DS Número] IN { 6; 7 } RETURN EsFinDeSemana
El resultado hasta ahora:
Días Festivos (Función RELATED)
Es momento de identificar si es una fecha festiva o no, recordemos que ese dato se encuentra en la tabla que la relacionamos al principio con la tabla de Calendario
Al ser una relación uno a uno podemos utilizar la función RELATED y así traer el dato de la festividad, esto quiere decir que la expresión:
FestividadDescripción =
RELATED ( FechasFestivas[Descripción] )
Devolvería la descripción en aquellos registros que coincidan con un día festivo y BLANK si no coincide
Nuestra tarea va a mitad de recorrido, dado que queremos un valor booleano (TRUE o FALSE), VERDADERO si es una festividad, FALSO si no.
Apoyándonos del BLANK arrojado por RELATED podemos utilizar la función ISBLANK así:
NoEsFestividad =
ISBLANK ( RELATED ( FechasFestivas[Fecha de Festividad] ) )
No obstante, nos invierte el orden que necesitamos en el TRUE/FLASE, quedando FALSO si es festividad y VERDADERO si no lo es. No te preocupes, que invertirlo es cuestión de implementar la función NOT
EsFestividad =
NOT ISBLANK ( RELATED ( FechasFestivas[Fecha de Festividad] ) )
Hasta ahora lo hemos realizado como si fuera en su propia columna calculada, pero, vamos a integrarlo con ayuda de variables a la expresión de la columna calculda principal, con lo cual quedaría algo así:
Día Laboral =
VAR EsFestivo =
NOT ISBLANK ( RELATED ( FechasFestivas[Descripción] ) )
VAR EsFinDeSemana = Calendario[DS Número] IN { 6; 7 }
RETURN
EsFestivo
Consolidado: Días Laborales
Para ir concluyendo la creación de la columna calculada de día laboral, vamos a realizar la siguiente pregunta:
¿No es festivo ni Fin de Semana?,
Con lo cual esa expresión se puede plantear así:
NoEsFestivoNiFDS =
NOT ( EsFestivo || EsFinDeSemana )
Previamente comenté que lo mejor es utilizar los números 1 y 0. 1 para día laboral y 0 para día no laboral, puesto que esto facilitará las medidas más adelante, bien consolidando todo nuestra expresión de la columna calculada completa queda:
Día Laboral =
VAR EsFestivo =
NOT ISBLANK ( RELATED ( FechasFestivas[Descripción] ) )
VAR EsFinDeSemana = Calendario[DS Número] IN { 6; 7 }
VAR DiaLaboral =
IF ( NOT ( EsFestivo || EsFinDeSemana ); 1; 0 )
RETURN
DiaLaboral
He aquí el resultado en la interfaz de Power BI:
Medida para Ingresos por Día Laboral
La primera medida es visualizar el número de días laborales en cada uno de los meses a lo largo de los distintos años.
Para ello, inicialmente creamos una matriz donde en el área de filas agregamos el campo Mes Nombre de la tabla de Calendario y el campo Año también de la tabla de Calendario en el área de columnas
Con la matriz preparada, ahora si vamos a crear una medida para contar el número de días laborales, la expresión DAX es sencilla:
#DíasLab =
SUM ( Calendario[Día Laboral] )
Con lo cual la añadimos al área de valores de la matriz previa:
Ingresos por Día Laboral (Caso Simple) – Función DIVIDE
Una medida común con días laborales consiste en calcular los ingresos generados por cada día laboral.
Esta expresión DAX también en simple, consiste en tomar la medida que realiza la suma de los ingresos:
Ingresos Tot =
SUM ( Pedidos[Ingresos] )
Y dividirla entre la medida recién creada de número de días laborales, claro que le mejor alternativa para división será siempre la función DIVIDE, por lo tanto, así queda la expresión:
IngxDíaLab =
DIVIDE ( [Ingresos Tot]; [#DíasLab] )
Quitamos la medida [#DíasLab] y llevamos la medida [IngxDíaLab]
El Problema del Total de Columnas (Función FILTER)
Pero Cuidado !!!!
La medida no es correcta para todos los casos, en particular para los totales
Permíteme y me explicó:
El total para el año 1999 está dividendo los ingresos totales del año 1999 entre el número de días laborales totales del mismo año, sin embargo, esto es falso, ya que como se puede observar en la matriz la compañia tiene valor de ingresos solo para diciembre, ya que la empresa DISPRODUCTOS inicio operaciones en diciembre de 1999.
Para ilustrarlo voy a llevar las tres medidas a ( [Ingresos Tot] , [#DíasLab] , [IngxDíaLab] ) a la matriz:
Debe ser divido entre 22 porque fueron los días laborales aplicables, y no entre la suma de todos los días laborales del año, puesto que la compañía no existía previo a Diciembre de 1999.
— ¿Cómo mejorar la medida?
Abogando por nuestro conocimiento de contextos y la función CALCULATE, centrémonos en el análisis de la casilla del total de columnas para 1999, para así analizar cuál sería la modificación en el contexto de filtro que deberíamos hacer:
El contexto de filtro tendría «nativamente» el filtro del año 1999, en nuestras manos queda inyectar un nuevo filtro que inyecte el mes de Diciembre:
Lo previo nos lleva inferior que: debemos generar una expresión tabular que genere una lista de meses sólo si los ingresos asociados para el mes y el año son estrictamente mayores a cero.
Generar una lista de meses válidos que respten el contexto se lográ con la función VALUES:
=
VALUES ( Calendario[Mes Nombre] )
Resalto que: respete el contexto, porque cuando la casilla de análisis no sea el total general esto debe ser así para que arroje los valores correctos, ahora bien, cuando estemos en el total general y no habrá ningún filtro para meses, por consiguiente, la función VALUES retorna la lista de todos los meses
Aspecto clave: La lista de meses deriva de la tabla de Calendario, pues, así lo dice la expresión DAX: VALUES( Calendario[Mes Nombre] )
Por lo anterior, es como si esta tabla temporalmente creada en la casilla quedará relacionada con las tablas que también tiene relación con la tabla de calendario, en concreto, con la tabla Pedidos.
El escenario que nos atañe ahora debe verificar si el mes tiene ingresos según el contexto (año 1999 porque estamos en 1999), para conseguirlo debemos ir comprobando uno a uno, es decir, fila a fila
Mejor dicho, iterar en nuestra tabla de meses generada con la función VALUES
La consideración es que, los ingresos se encuentran en la tabla Pedidos, y vamos a iterar en una tabla derivada de la tabla de Calendario, por lo tanto, debemos realizar transación de contextos
=
FILTER ( VALUES ( Calendario[Mes Nombre] ); [Ingresos Tot] )
El filtro anterior no se encuentra completo puesto que queremos dejar los meses que tengan ingresos, para ello basta con indicar en la expresión de Filter que el [Ingresos Tot] se diferente de 0 →
=
FILTER ( VALUES ( Calendario[Mes Nombre] ); [Ingresos Tot] <> 0 )
Muy bien, el anterior sería la expresión tabular a inyectar al contexto de filtro mediante la función CALCULATE y así evaluar la medida de [#DíasLab] correctamente, en conclusión, así sería el denominador:
=
CALCULATE (
[#DíasLab];
FILTER ( VALUES ( Calendario[Mes Nombre] ); [Ingresos Tot] <> 0 )
)
Consolidando todo, la medida para los ingresos por cada día laboral queda finalmente así:
IngxDiaLab-Parcial =
VAR DiasLab =
CALCULATE (
[#DíasLab];
FILTER ( VALUES ( Calendario[Mes Nombre] ); [Ingresos Tot] <> 0 )
)
VAR Resultado =
DIVIDE ( [Ingresos Tot]; DiasLab )
RETURN
Resultado
Apreciemos el resultado en nuestra Matriz de Meses y Años
La medida para el año 1999 ha quedado bien véase el total de columnas para ese año.
El Problema del Total General (Función CROSSJOIN)
La cuestión es que no todo es color de rosa, la medida anterior es parcialmente correcta, porque si ahora nos vamos al total general y analizamos la medida [IngxDíaLab-Parcial] y el contexto del total general, nos daremos cuenta de que sumara los días laborales de todos los años y todos los meses
Básicamente porque el contexto de filtro está vacío, es decir, la tabla no tiene ningún filtro previo aplicado, por lo tanto, se encuentran completas.
Al inyectar la expresión de FILTER y VALUES esta ira iterando mes a mes y evaluado si el total de ingresos es diferente de cero, el punto aquí es que no hay filtro de año, lo que conlleva a que en cada mes siempre se mayor que cero, pues que esta sumando los meses de todos los años
— ¿Cómo dar solución?
La tabla a ser inyectada que supla este inconveniente en realidad debe proporcionar todas las combinaciones de años y meses.
La función que hace dicha tarea es CROSSJOIN, dado que ejecuta el producto cartesiano de las tablas que se pasen a sus parámetros.
Como nuestra situación requerimos columnas, pues eso será lo que pasemos a los argumentos de CROSSJOIN, por un lado la columna de años y por el otro la columna de meses
=
CROSSJOIN ( VALUES ( Calendario[Año] ); VALUES ( Calendario[Mes Nombre] ) )
Visualmente (para nuestro caso) luciría así:
FILTER iterará en todas estas combinaciones, y allí si, cuando encuentre que para un mes y un año específico no se generará ingresos entonces este mes y año no queda en la tabla final que arroja FILTER. Para nuestro caso particular la tabla quedará algo del siguiente estilo:
Nuesta expresión DAX Completa:
IngxDiaLab-COM =
VAR DiasLab =
CALCULATE (
[#DíasLab];
FILTER (
CROSSJOIN ( VALUES ( Calendario[Mes Nombre] ); VALUES ( Calendario[Año] ) );
[Ingresos Tot] <> 0
)
)
VAR Resultado =
DIVIDE ( [Ingresos Tot]; DiasLab )
RETURN
Resultado
En la matriz:
Una hermosa expresión DAX!
Otros Periodos de tiempo (Semestres y Trimestres)
Si deseas utilizar la expresión anterior para segmentación en el tiempo más grande que meses, por ejemplo, trimestres o semestres también funcionará correctamente
El inconveniente vendría si necesitas escalas menores, como quincenas y semanas, sin embargo, el tratamiento de semanas es un mundo completo en si
Por favor, regálame tus comentarios, no me gusta sentirme sólo en estos artículos 😉 Además tu retroalimentación me ayuda muchísimo. Estaré leyendo y respondiendo todos los comentarios.
— ¿Seguimos profundizando en esta temática en Power BI?
Estamos charlando en la sección de comentarios
— Miguel Caballero