Años Bisiestos en Power BI con DAX.
En este artículo te hablaré de como detectar años bisiestos en Power BI con el lenguaje DAX, además estudiamos una breve medida de aplicación.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. Acerca del Calendario Estándar (Gregoriano)
a. Regala para Calcular Años Bisiestos
b. Determinar Años Bisiesto en Power BI con DAX
2. Comparación respecto al año anterior (% YOY)
a. Días desiguales en año bisiesto
b.% YOY Ingresos en año bisiesto
Hola, Hola …
Y recibe un cálido saludo de mi parte
Comentarte inicialmente que existen muchas maneras de trabajar con años bisiestos, una manera sencilla es cargar en tu modelo una tabla que liste los años bisiestos, de aquí puedes descargar una lista del 1900 al 2100.
Sin embargo, en el presente artículo vamos a realizarla netamente con DAX.
Ajústate en el cinturón que vamos a arrancar.
Acerca del Calendario Gregoriano
El calendario que todos conocemos y manejamos comúnmente tiene un problema:
En realidad, la tierra no le toma 365 días exactos darle una vuelta completa al sol (esa es la definición de año)
— Le toma 365 días y un cuarto de día.
Esto quiere decir que cada 4 año nos hemos atrasado un día completo, porque guardamos ese cuarto de día en el bolsillo.
Es por lo anterior que se le agrega un día entero al mes más corto: febrero, cada 4 años para compensar y alienar todo nuevamente.
A estos años se les conoce como años bisiestos o leap year en inglés, done leap significa salto, por eso se asocia una ranita (de allí la portada del presente artículo, además, puse la «ranita» como un ninja porque tenemos ser un que poco ninjas en DAX para crear las expresiones que se vienen más adelante en este post)
Pero cuidado, la verdad es que la tierra tarda un poco menos de 365 días y un cuarto de día en darle una vuelta completa al sol, por lo que cada cuatro años cuando añadimos un día nos estamos pasando un pelín, haciendo que todo el sistema se desfase de nuevo después de un transcurso de años considerable, algo así como 3 días cada 100 años.
Para hacer las cosas más complicadas la parte fraccionaría del día tiene varios decimales: 365.2419
De allí que la regla para determinar si un año es bisiesto o no sea un poco compleja.
No nos vamos detener aquí, vamos la regla general para calcular un año bisiestos y de allí nos vamos a DAX.
A pesar de llo, si quieres conocer más acerca definición de año bisiesto, te recomiendo: este vídeo, este vídeo y este vídeo.
Regla para Calcular Año Bisiesto
Si organizamos todas las ideas, llegamos a que un año bisiesto se determina así:
.
- Es bisiesto si es divisible entre 4.
- Pero no es bisiesto si es divisible entre 100.
- Pero sí es bisiesto si es divisible entre 400.
.
Mejor dicho:
— ¿Cómo lo llevamos a DAX?
Determinar Año Bisiesto en Power BI con DAX
Tenemos la fórmula lógica para determinar si un año es bisiesto.
Además, de nuestro conocimiento de aritmética sabemos que un número es divisible en otro si el residuo es cero (0). La función DAX para calcular el residuo es la función MOD.
— Algunos ejemplos:
=
MOD ( 0; 2 ) — Retorna 0
=
MOD ( 5; 2 ) — Retorna 1
=
MOD ( 6; 3 ) — Retorna 0
=
MOD ( 7; 3 ) — Retorna 1
=
MOD ( 8; 3 ) — Retorna 2
Por lo anterior, hallar si el año es divisible en 4 o no, sólo basta con hacer lo siguiente:
=
MOD ( 2000; 4 ) = 0 — ¿Número divisible en 4?, Retorna TRUE()
Lo mismo para ver si es divisible en 100 y en 400:
=
MOD ( 2000; 100 ) = 0 — ¿Número divisible en 100?, Retorna TRUE()
=
MOD ( 2000; 400 ) = 0 — ¿Número divisible en 400?, Retorna TRUE()
Ya tenemos todas las piezas del puzzle, por lo que nuestra medida para determinar si un año es bisiesto, quedaría:
EsBisiesto =
VAR AnSeleccionado =
INT ( VALUES ( Calendario[Año] ) )
VAR DivisibleEn4 =
MOD ( AnSeleccionado; 4 ) = 0
VAR DivisibleEn100 =
MOD ( AnSeleccionado; 100 ) = 0
VAR DivisibleEn400 =
MOD ( AnSeleccionado; 400 ) = 0
VAR EsAnBisiesto =
IF (
( DivisibleEn4 && NOT ( DivisibleEn100 ) )
|| DivisibleEn400;
TRUE ();
FALSE ()
)
RETURN
EsAnBisiesto
Es viable mejorar la medida de [EsBisiesto] si nos aseguramos que sólo exista un año seleccionado, de lo contrario que devuelva un error indicando que se debe seleccionar sólo uno.
EsBisiesto =
IF (
HASONEVALUE ( Calendario[Año] );
VAR AnSeleccionado =
INT ( VALUES ( Calendario[Año] ) )
VAR DivisibleEn4 =
MOD ( AnSeleccionado; 4 ) = 0
VAR DivisibleEn100 =
MOD ( AnSeleccionado; 100 ) = 0
VAR DivisibleEn400 =
MOD ( AnSeleccionado; 400 ) = 0
VAR EsAnBisiesto =
IF (
( DivisibleEn4 && NOT ( DivisibleEn100 ) )
|| DivisibleEn400;
TRUE ();
FALSE ()
)
RETURN
EsAnBisiesto;
ERROR ( «Por favor seleccione un año especifico para determinar si es específico « )
)
Prefiero la función ERROR en este caso dado que no permite la ejecución de ningún cálculo que involucre años bisiestos, no obstante, puedes cambiar esa función por BLANK, por un texto por lo que desees.
— Y … ¿Para que #@!! puede ser útil determinar si un año es bisiesto en Power BI con DAX?
Comparación Respecto al Año Anterior (YOY)
Una métrica típica con periodos de tiempo consiste en calcular el porcentaje de crecimiento (o decrecimiento) respecto al año anterior.
Definamos un par de medidas para ello:
Ingresos Tot =
SUM ( pedidos[Ingresos] )
Y la segunda medida:
PY Ingresos =
CALCULATE (
SUM ( Pedidos[Ingresos] );
SAMEPERIODLASTYEAR ( Calendario[Fecha] )
)
Por lo tanto el porcentaje de variación (Year over year / % YOY) lo podemos determinar así:
% YOY Ingresos =
DIVIDE (
[Ingresos Tot] – [PY Ingresos];
[PY Ingresos]
)
Por cierto, si deseas crear todo en una única medida, una opción utilizando variables puede ser como sigue:
% YOY Ingresos =
VAR IngresoAnActual =
SUM ( Pedidos[Ingresos] )
VAR IngresoAnAnterior =
CALCULATE (
SUM ( Pedidos[Ingresos] );
SAMEPERIODLASTYEAR ( Calendario[Fecha] )
)
RETURN
DIVIDE (
IngresoAnActual – INgresoAnAnterior;
INgresoAnAnterior
)
Estimado lector o lectora, si no tienes ni idea de las funciones de inteligencia de tiempo, de las tablas de calendario y cómo utilizar este tipo de cálculos, entonces →
¿Qué esperas?
En nuestro canal de YouTube, encontraras el seminario: Cálculos de Inteligencia de Tiempo con Power BI (DAX)
Allí podrás aprender toda la fundamentación de este tipo de funciones a partir del minuto 24:21.
Esto lo podemos presentar en una matriz donde tenemos los meses en columnas y seleccionamos el año en una segmentación de datos, así:
Frena en seco, por que la comparación anterior no es justa:
Días Desiguales en Año Bisiesto
Resulta que, si analizamos, por ejemplo, enero, estamos comparando los ingresos de 31 días del 2012 con los ingresos de 31 días del año anterior 2011, por consiguiente, es viable decir que es una comparación justa debido a que es el contraste de los ingresos en la misma cantidad de días (31).
Empero, para el mes de febrero no es así, específicamente porque el año 2012 es bisiesto, quiere decir que febrero viene equipado con 29 días, por lo tanto, para este año el ingreso ([Ingresos Tot]) corresponde a 29 días, no obstante, el año anterior no es bisiesto, queriendo decir que los ingresos de [PY Ingresos] es de 28 días.
Podríamos decir, en el caso de febrero que no es una comparación justa, ya que no corresponde al mismo número de días.
Por si fuera poco, esto va a ser así para todos los años bisiesto, como: 2000, 2004, 2008, 2012, 2016, 2020, etc.
El tratamiento a este escenario se puede abordar de muchas maneras, un enfoque es detectar si el año es bisiesto y si es así quitar el día 29 del mes de febrero para que así realice la comparación con 28 días.
Vamos a tomar este caso aquí en este artículo.
% YOY Ingresos en Año Bisiesto
Lo primero es corroborar que sea un año bisiesto, si no es así simplemente llamamos a nuestra medida clásica [% YOY Ingresos], si es un año bisiesto debemos proceder con nuestro cálculo de la variación con respecto del año anterior quitando el 29 de febrero del año correspondiente.
Para ir aproximándonos:
% YOY IngresosBisiesto =
IF (
[EsBisiesto];
«Calcular variación porcentaul quitando febrero»;
[% YOY Ingresos]
)
Quitar el 29 de febrero de las fechas actuales en el contexto de filtro, lo podemos conseguir iterando en la lista de fechas únicas con FILTER y decirle que sea distinto del «29/02/AñoActual», así:
EVALUATE
VAR AnAcutal =
VALUES ( Calendario[Año] )
VAR FechasActualesSin29deFeb =
FILTER (
VALUES ( Calendario[Fecha] );
Calendario[Fecha] <> DATE ( AnAcutal; 2; 29 )
)
RETURN
FechasActualesSin29deFeb
Teniendo el nuevo filtro con la magia de CALCULATE podemos conseguir el cálculo de los ingresos totales en el contexto modificado, en definitiva, nuestra expresión completa quedaría:
% YOY IngresosBisiesto =
IF (
[EsBisiesto];
VAR AnAcutal =
VALUES ( Calendario[Año] )
VAR FechasActualesSin29deFeb =
FILTER (
VALUES ( Calendario[Fecha] );
Calendario[Fecha] <> DATE ( AnAcutal; 2; 29 )
)
VAR IngActualesSin29deFeb =
CALCULATE ( [Ingresos Tot]; FechasActualesSin29deFeb )
RETURN
DIVIDE ( IngActualesSin29deFeb – [PY Ingresos]; [PY Ingresos] );
[% YOY Ingresos]
)
El resultado y diferencia en % YOY Ingresos y % YOY Ingresos Bisiesto lo podemos observar en el mes de febrero y el total general teniendo un año bisiesto seleccionado, por ejemplo:
Si es un año bisiesto, los cálculos coinciden:
Evidentemente hay muchas maneras de conseguir este cálculo, y probablemente se te ocurra una mejor manera, aquí les comparto una opción.
Eso es todo por hoy.
Por favor, regálame tus comentarios, tus experiencias con esta temática y si tienes alguna manera de trabajar con años bisiestos, como siempre 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.
Estamos charlando en la sección de comentarios.
— Miguel Caballero