Introducción
Time Intelligence basada en calendario, la necesidad de una lógica de Time Intelligence personalizada ha disminuido drásticamente.
Ahora podemos crear calendarios personalizados para satisfacer nuestras necesidades de cálculo de Time Intelligence.
Es posible que hayas leído mi artículo sobre inteligencia temporal avanzada:
La mayor parte de la lógica personalizada ya no es necesaria.
Pero todavía tenemos escenarios en los que debemos tener cálculos personalizados, como el promedio móvil.
Hace algún tiempo, SQLBI escribió un artículo sobre el cálculo del promedio móvil.
Este artículo utiliza los mismos principios descritos allí con un enfoque ligeramente diferente.
Veamos cómo podemos calcular el promedio móvil durante tres meses utilizando los nuevos Calendarios.
Usando la clásica Inteligencia del Tiempo
Primero, utilizamos el calendario gregoriano estándar con la tabla de fechas clásica de Time Intelligence.
Utilizo un enfoque similar al descrito en el artículo de SQLBI vinculado en la sección Referencias a continuación.
Promedio acumulado por mes = // 1. Obtenga la primera y la última fecha para el contexto de filtro actual VAR MaxDate = MAX( ‘Fecha'[Date] ) // 2. Generar el rango de fechas necesario para la media móvil (tres meses) VAR DateRange = DATESINPERIOD( ‘Fecha'[Date]
,MaxDate,-3,MONTH) // 3. Genere una tabla filtrada por el rango de fechas generado en el paso 2 // Esta tabla contiene solo tres filas VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( ‘Fecha'[MonthKey]
“#Ventas”, [Sum Online Sales]
) ,DateRange ) RETURN // 4. Calcule el promedio sobre los tres valores en la tabla generada en el paso 3 AVERAGEX(SalesByMonth, [#Sales])
Al ejecutar esta medida en DAX Studio, obtengo los resultados esperados:
Hasta ahora, todo bien.
Usando un calendario estándar
A continuación, creé un calendario llamado “Calendario gregoriano” y cambié el código para usar este calendario.
Para que esto sea más fácil de entender, copié la tabla de fechas en una nueva tabla llamada “Tabla de fechas gregoriana”.
El cambio es al llamar a la función DATESINPERIOD().
En lugar de usar la columna de fecha, uso el calendario recién creado:
Promedio móvil por mes = // 1. Obtenga la primera y la última fecha para el contexto de filtro actual VAR MaxDate = MAX( ‘Tabla de fechas gregorianas'[Date] ) // 2. Generar el rango de fechas necesario para la media móvil (tres meses) VAR DateRange = DATESINPERIOD( ‘Calendario gregoriano’ ,MaxDate ,-3 ,MONTH ) // 3. Generar una tabla filtrada por el rango de fechas generado en el paso 2 // Esta tabla contiene solo tres filas VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( ‘Tabla de fechas gregorianas'[MonthKey]
“#Ventas”, [Sum Online Sales]
) ,DateRange ) RETURN // 4. Calcule el promedio sobre los tres valores en la tabla generada en el paso 3 AVERAGEX(SalesByMonth, [#Sales])
Como era de esperar, los resultados son idénticos:
El rendimiento es excelente, ya que esta consulta se completa en 150 milisegundos.
Usando un calendario personalizado
Pero, ¿qué sucede cuando se utiliza un calendario personalizado?
Por ejemplo, ¿un calendario con 15 meses por año y 31 días por cada mes?
Creé un calendario de este tipo para mi artículo, que describe casos de uso de Time Intelligence basado en calendario (consulte el enlace en la parte superior y en la sección Referencias).
Cuando miras el código de la medida, notarás que es diferente:
Promedio móvil por mes (personalizado) = VAR LastSelDate = MAX(‘Calendario financiero'[CalendarEndOfMonthDate]) VAR MaxDateID = CALCULATE(MAX(‘Calendario financiero'[ID_Date]) ,REMOVEFILTERS(‘Calendario financiero’) ,’Calendario financiero'[CalendarEndOfMonthDate] = LastSelDate ) VAR MinDateID = CALCULATE(MIN(‘Calendario financiero'[ID_Date]) ,REMOVEFILTERS(‘Calendario financiero’) ,’Calendario financiero'[CalendarEndOfMonthDate] = EOMONTH(LastSelDate, -2) ) VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( ‘Calendario financiero'[CalendarYearMonth]
“#Ventas”, [Sum Online Sales]
), ‘Calendario financiero'[ID_Date] >= MinDateID && ‘Calendario financiero'[ID_Date] <= MaxDateID ) RETORNO PROMEDIO(VentasPorMes, [#Sales])
El motivo de los cambios es que esta tabla carece de una columna de fecha que se pueda utilizar con la función DATESINPERIOD(). Por este motivo, debo utilizar un código personalizado para calcular el rango de valores de ID_Date.
Estos son los resultados:
Como puedes comprobar, los resultados son correctos.
Optimización mediante el uso de un índice de días
Pero cuando analizo el rendimiento, no es tan bueno.
Se necesitan casi medio segundo para calcular los resultados.
Podemos mejorar el rendimiento eliminando la necesidad de recuperar el ID_Date mínimo y máximo y realizando un cálculo más eficiente.
Sé que cada mes tiene 31 días.
Para retroceder tres meses, sé que debo retroceder 93 días.
Puedo usar esto para crear una versión más rápida de la medida:
Promedio móvil por mes (financiero) = // Paso 1: obtener el último mes (ID) VAR SelMonth = MAX(‘Calendario financiero'[ID_Month]) // Paso 2: Generar el rango de fechas de los últimos 93 días VAR DateRange = TOPN(93 ,CALCULATETABLE( SUMMARIZECOLUMNS(‘Calendario financiero'[ID_Date]) ,REMOVEFILTERS(‘Calendario financiero’) ,’Calendario financiero'[ID_Month] <= SelMonth), 'Calendario financiero'[ID_Date]DESC ) // 3. Genere una tabla filtrada por el rango de fechas generado en el paso 2 // Esta tabla contiene solo tres filas VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( 'Calendario financiero'[ID_Month] "#Ventas", [Sum Online Sales] ) ,DateRange ) RETURN // 4. Calcule el promedio sobre los tres valores en la tabla generada en el paso 3 AVERAGEX(SalesByMonth, [#Sales])
Esta vez, utilicé la función TOPN() para recuperar las 93 filas anteriores de la tabla Calendario financiero y utilicé esta lista como filtro.
Los resultados son idénticos a la versión anterior:
Esta versión necesita sólo 118 ms para completarse.
¿Pero podemos ir aún más lejos con la optimización?
A continuación, agregué una nueva columna al Calendario Fiscal para asignar rangos a las filas. Ahora, cada fecha tiene un número único que está en correlación directa con el orden de las mismas:
La medida que utiliza esta columna es la siguiente:
Promedio móvil por mes (financiero) = // Paso 1: obtener el último mes (ID) VAR MaxDateRank = MAX(‘Calendario financiero'[ID_Date_RowRank]) // Paso 2: Generar el rango de fechas de los últimos 93 días VAR DateRange = CALCULATETABLE( SUMMARIZECOLUMNS(‘Calendario financiero'[ID_Date]) ,REMOVEFILTERS(‘Calendario financiero’) ,’Calendario financiero'[ID_Date_RowRank] <= MaxDateRank && 'Calendario financiero'[ID_Date_RowRank] >= MaxDateRank – 92) –ORDENAR POR ‘Calendario financiero'[ID_Date] DESC // 3. Generar una tabla filtrada por el rango de fechas generado en el paso 2 // Esta tabla contiene solo tres filas VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( ‘Calendario financiero'[ID_Month]
“#Ventas”, [Sum Online Sales]
) ,DateRange ) RETURN // 4. Calcule el promedio sobre los tres valores en la tabla generada en el paso 3 AVERAGEX(SalesByMonth, [#Sales])
El resultado es el mismo, no lo vuelvo a mostrar.
Pero aquí está la comparación de las estadísticas de ejecución:
Como puede ver, la versión que usa TOPN() es ligeramente más lenta que la que usa la columna RowRank.
Pero las diferencias son marginales.
Más importante aún, la versión que utiliza la columna RowRank requiere más datos para completar los cálculos. Consulte la columna Filas para obtener más detalles.
Esto significa más uso de RAM.
Pero con este pequeño número de filas, las diferencias siguen siendo marginales.
Es tu elección qué versión prefieres.
Usando un calendario semanal
Por último, veamos un cálculo semanal.
Esta vez, quiero calcular el promedio móvil de las últimas tres semanas.
Como Time Intelligence basado en calendario permite la creación de un calendario semanal, la medida es muy similar a la segunda:
Promedio móvil por semana = // 1. Obtenga la primera y la última fecha para el contexto de filtro actual VAR MaxDate = MAX( ‘Tabla de fechas gregorianas'[Date] ) // 2. Generar el rango de fechas necesario para la media móvil (tres meses) VAR DateRange = DATESINPERIOD( ‘Week Calendar’ ,MaxDate ,-3 ,WEEK ) // 3. Generar una tabla filtrada por el rango de fechas generado en el paso 2 // Esta tabla contiene solo tres filas VAR SalesByMonth = CALCULETETABLE( SUMMARIZECOLUMNS( ‘Tabla de fechas gregorianas'[WeekKey]
“#Ventas”, [Sum Online Sales]
) ,DateRange ) RETURN // 4. Calcule el promedio sobre los tres valores en la tabla generada en el paso 3 AVERAGEX(SalesByMonth, [#Sales])
La parte clave es que uso el parámetro “SEMANA” en la llamada DATESINPERIOD().
Eso es todo.
Este es el resultado de la consulta:
El rendimiento es excelente, con tiempos de ejecución inferiores a 100 ms.
Tenga en cuenta que los cálculos semanales solo son posibles con Time Intelligence basado en calendario.
Conclusión
Como has visto, Time Intelligence basado en calendario nos hace la vida más fácil con lógica personalizada: sólo necesitamos pasar el calendario en lugar de una columna de fecha a las funciones. Y podemos calcular intervalos semanales.
Pero el conjunto de funciones actual no incluye un intervalo semestral. Cuando debemos calcular resultados semestrales, debemos usar Time Intelligence clásico o escribir código personalizado.
Pero todavía necesitamos una lógica personalizada, especialmente cuando no tenemos una columna de fecha en nuestra tabla de calendario. En tales casos, no podemos utilizar las funciones de inteligencia horaria estándar, ya que todavía funcionan con columnas de fecha.
Recuerde: la tarea más importante cuando se trabaja con Time Intelligence basada en calendario es crear una tabla de calendario consistente y completa. Desde mi experiencia, esta es la tarea más compleja.
Como nota al margen, encontré algunas funciones interesantes en daxlib.org sobre un promedio móvil.
Agregué un enlace a las funciones en la sección Referencias a continuación.
Estas funciones siguen un patrón completamente diferente, pero quería incluirlas para crear una imagen completa de este tema.
Referencias
El artículo mencionado de SQLBI.com sobre el cálculo del promedio móvil:
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax
Time Series funciona en daxlib.org con un enfoque diferente:
https://daxlib.org/package/TimeSeries.MovingAverage
Aquí está mi último artículo, donde explico la inteligencia del tiempo basada en calendario:
How to Implement Three Use Cases for the New Calendar-Based Time Intelligence
Como en mis artículos anteriores, utilizo el conjunto de datos de muestra de Contoso. Puede descargar el conjunto de datos ContosoRetailDW de forma gratuita desde Microsoft aquí.
Los datos de Contoso se pueden utilizar libremente bajo la licencia MIT, como se describe en este documento. Cambié el conjunto de datos para cambiar los datos a fechas contemporáneas.