Hola Explorador@s de datos!

Cada vez más es más importante en las empresas conocer de forma detallada la evolución de los valores a través del tiempo para permitir tomar mejores decisiones de negocio.

Seguro que alguna vez has necesitado calcular cuanto se ha vendido en mayo de 2015 o cuántos clientes diferentes me han comprado entre el 1 y el 15 del mes de septiembre.

Este tipo de calculos podemos implementarlos de forma sencilla porque tenemos identificado el periodo de forma clara, pero… ¿Qué ocurre si el requisito para nuestro cálculo es sumar las ventas de los últimos 15 días o calcular el promedio de las ventas de los últimos 4 meses?.

Estos requerimientos parten de una base que es dinámica, el día de hoy sólo es hoy, mañana habrá cambiado, por tanto, cada vez que se actualicen los datos, estos parámetros marcarán periodos distintos que provocarán cambios en los resultados.

Para resolverlo, la mejor opción (como casi siempre), será enriquezer en modelo de datos para facilitarnos los cálculos DAX y esta vez lo haremos basandonos en un concepto muy interesante llamado desvío (offset en inglés).

Desvíos

El concepto de desvío no es algo nuevo y Ralph Kimball ya lo comentaba en su libro DataWareHouse ToolKit cuando habla de los OFFSET. El Desvío se utiliza principalmente con la dimensión calendario y es algo tan simple como calcular en una columna adicional en la dimensión la distancia existente entre el día de hoy y la fecha que representa cada fila de la dimensión.

Esta «distancia» puede estar calculada en días, semanas, meses, semestres, años o en el periodo que necesites, lo que facilitará y simplificará enormemente los futuros cálculos dinámicos que necesites, incluso, nos permitirá agregar estas columnas dinámicas de desvío como filtros predeterminados en nuestros visuales o páginas para conseguir el efecto de que al abrir un informe, de forma predeterminada se muestre el mes actual o el año actual.

Estas columnas de desvíos pueden calcularse tanto en DAX, como en PQ o en el origen de los datos y como siempre, la recomendación sería calcularlas «lo más lejos posible de Power BI».

Para el ejemplo, vamos a utilizar Power Query como herramienta para obtener las columnas de desvíos de días y meses.

Partiremos de un modelo donde ya tenemos definida una dimensión Calendario con las siguientes columnas:

En este caso la tabla calendario contendrá las fechas del año 2024 (del 1 de enero al 31 de diciembre).

A partir de esta tabla, comenzamos añadiendo una variable que almacene la fecha del día de hoy. Esta variable la agregaremos utilizando el editor avanzado de Power Query y le llamaremos vHoy. Para calcularla, utilizaremos la siguiente función:

vHoy = Date.From( DateTime.LocalNow () ),

A partir de este momento, ya disponemos de una variable vHoy que vamos a poder utilizar para generar las columnas de los desvíos.

Comencemos por calcular algo muy simple. Una columna que en nuestro calendario nos identifique si una fecha es Pasado, Presente o Futuro. Para ello, agregaremos una nueva columna condicional que comparará la el valor de la columna Fecha de cada fila con la variable vHoy para determinar el momento en el que estamos. Siempre tengo dudas en como llamar a esta columna, pero el nombre de [Momento] me parece adecuado.

Vamos a la pestaña Agregar Columna –> Columna Personalizada y agregamos el siguiente código:

= if [Fecha] < vHoy then "Pasado" else if [Fecha]= vHoy then "Presente" else "Futuro"

Sobre la nueva columna definimos que el valor sea de tipo texto y así tenemos de momento nuestra dimensión calendario.

La siguiente columna, es decir, el siguiente «Desvío» que vamos a calcular será calcular cuántos días de diferencia hay desde la fecha de cada fila con respecto al día de hoy. Esta columna nos permitirá de forma rápida por ejemplo realizar operaciones con fechas de los últimos 10 días o con los 15 siguientes a la fecha del contexto.

El cálculo será una simple resta entre la [Fecha] de la fila actual menos el valor de la variable vHoy que almacena la fecha del día actual. Esta nueva columna la vamos a generar también desde el menú Agregar columna –> Columna personalizada y la definiremos con el nombre [DesvíoDías].

Después de agregar la columna, es muy importante recordar que deberemos cambiar el tipo de dato de la nueva columna a número entero.

Cómo un último ejemplo vamos a crear una columna que cálcule el [DesvíoMes] existente entre el mes de la fecha de cada fila y el mes de la fecha actual.

Para realizar este cálculo, tenemos también que considerar la posibilidad de que nuestro calendario tenga más de 1 año, por lo que la simple resta entre el mes de la fila y el mes de la fecha de hoy no vale. A ese valor, tendremos que sumarle 12 por cada año de distancia que exista entre las fechas.

La fórmula que vamos a utilizar para calcularla será la siguiente:

([Año] - Date.Year(vHoy)) * 12 + ([Mes] - Date.Month(vHoy))

Aquí podemos observar el resultado actual de la dimensión calendario con la creación de las columnas [Momento], [DesvíoDía] y [DesvíoMes]

Bueno, pues ahora que ya tenemos configurados algunos de estos desvíos, vamos a ver su aplicación práctica en un informe de Power BI.

Escenario 1:

Queremos un informe dónde al iniciarlo SIEMPRE, aparezcan de forma predeterminada las ventas de los últimos 15 días. Como podemos observar, no es posible establecer un filtro automático a nivel de fecha ya que sólo nos serviría para el día actual. Una opción simple podría ser utilizar el desvío de día para configurar en nuestro informe un filtro predeterminado aplicado a la página para que se filtren los datos con [DesvíoDia] >= 0 AND [DesvíoDía] <= 15.

Si aplicamos un filtro sobre la página utilizando la columna [DesvíoDía]…

La aplicación del filtro basada en la columna [DesvíoFecha] provoca que de forma totalmente dinámica se muestren los datos de los últimos 15 días.

Escenario 2:

En este segundo escenario, nos solicitan como requerimiento tener un listado de las ventas totales de nuestras tiendas junto con las ventas producidas en los últimos 15 días (tal como muestra la siguiente imagen).

Ahora, para realizar el cálculo de la medida Ultimos 15 días utilizaremos también la columna [DesvíoDía] que hemos creado en la dimensión calendario de la siguiente forma:

En conclusión, los desvíos en las dimensiones calendario de Power BI son una herramienta esencial para el análisis de datos y la toma de decisiones estratégicas.

Incorporar desvíos a nuestros proyectos nos permite mejorar mucho la funcionalidad y la experiencia de usuario.

Y para que no haya excusas para no utilizarlas, aquí os dejo para descargar la dimensión calendario en lenguaje M que yo suelo utilizar en todos mis proyectos donde encontraréis, a parte de los desvíos a nivel de día, mes, trimestre y año un montón más de columnas dimensionales que os permitirán dar un nuevo enfoque a vuestros proyectos.

Descarga la dimensión calendario en M aquí

Espero que el ejemplo os haya sido de mucha utilidad y que lo apliquéis y mejoréis en vuestros propios informes.

Nos vemos en próximos post y como siempre #SigueElCaminoDeLaEstrella