En estadística, una media móvil se refiere al cálculo de puntos de datos mediante la creación de una serie de promedios de diferentes subconjuntos del total de los datos originales con el objetivo de suavizar las fluctuaciones del corto plazo, resaltando así las tendencias o ciclos de largo plazo.
En este artículo vamos a ver diferentes formas de implementar una media móvil en Power BI, no con el objetivo de crear patrones predefinidos que podamos usar directamente en nuestros modelos, sino con el propósito de entender distintas formas de manipular el contexto de filtro y practicar nuestras habilidades para «pensar en DAX».
Medias móviles dinámicas con medidas
Si queremos analizar las ventas diarias de una empresa, el representar en una visualización de líneas el importe con la fecha en el eje de la misma no nos ayuda demasiado, ya que, como podemos observar en la siguiente visualización, es difícil entrever la tendencia debido a las fuertes variaciones diarias que se producen en los datos:
En casos como este puede ser una buena idea calcular, para cada punto de los datos, la media móvil, es decir, el valor promedio de las ventas sobre un periodo mayor al diario, de manera que nos permita detectar con mayor facilidad la tendencia a lo largo del tiempo.
La media móvil simple previa es la media aritmética de los n datos anteriores, por lo que cuanto más grande sea el valor escogido de n, mayor será la influencia de los datos antiguos. En contrapartida, si se selecciona una n baja se tendrán más en cuenta los datos recientes en la realización del cálculo. Su fórmula en DAX es la siguiente:
MediaMovil50 =VAR _UltimaFechaVisible =
MAX ( Calendario[Fecha] )
VAR _NumeroDias = 50
VAR _Periodo =
FILTER (
ALL ( Calendario[Fecha] );
AND (
Calendario[Fecha] <= _UltimaFechaVisible;
Calendario[Fecha] > _UltimaFechaVisible – _NumeroDias
)
)
VAR _Resultado =
CALCULATE ( AVERAGEX ( Calendario; [Ventas] ); _Periodo )
RETURN
_Resultado
En el ejemplo anterior hemos calculado la media móvil simple previa, es decir, la media aritmética de los 50 valores anteriores para cada punto del conjunto de datos. Otro tipo de media móvil simple corresponde a la denominada media móvil central, en la que, en lugar de utilizar sólo datos anteriores, se utilizan también datos posteriores a aquél del cual queremos obtener la media. Para elaborar dicho cálculo en DAX solo tenemos que modificar el horizonte temporal sobre el que calcularemos el promedio para cada punto, tomando los 25 datos anteriores y posteriores. La fórmula quedaría de la siguiente manera:
MediaMovilCentral50 =VAR _UltimaFechaVisible =
MAX ( Calendario[Fecha] )
VAR _NumeroDias = 50
VAR _Periodo =
FILTER (
ALL ( Calendario[Fecha] );
AND (
Calendario[Fecha] <= _UltimaFechaVisible + ( _NumeroDias / 2 );
Calendario[Fecha] >= _UltimaFechaVisible – ( _NumeroDias / 2 )
)
)
VAR _Resultado =
CALCULATE ( AVERAGEX ( Calendario; [Ventas] ); _Periodo )
RETURN
_Resultado
En la siguiente imagen podemos ver ambas medidas sobre la visualización anterior. Como se observa, las medias móviles suavizan las fluctuaciones diarias haciendo posible el análisis de la tendencia:
Es importante entender que cuando calculamos promedios, ya sea mediante la función AVERAGE o AVERAGEX, DAX ignora por completo los valores BLANK, es decir, si un día no existen ventas, dicho día no formará parte de la media. Si las necesidades de información del negocio requieren que el promedio se calcule tomando en cuenta todos los días del horizonte temporal definido, teniendo en cuenta también todos aquellos días en los que la suma de las ventas es igual a cero, podemos simplemente realizar el cálculo mediante una división, en lugar de usar la función AVERAGEX. Este cálculo es también más rápido que el anterior, al evitar la transición de contexto producida en la variable _Resultado. La fórmula en este caso sería la siguiente:
VAR _UltimaFechaVisible =
MAX ( Calendario[Fecha] )
VAR _NumeroDias = 50
VAR _Periodo =
FILTER (
ALL ( Calendario[Fecha] );
AND (
Calendario[Fecha] <= _UltimaFechaVisible;
Calendario[Fecha] > _UltimaFechaVisible – _NumeroDias
)
)
VAR _Resultado =
CALCULATE ( DIVIDE ( [Ventas]; COUNTROWS ( Calendario ) ); _Periodo )
RETURN
_Resultado
Como podemos percibir en la siguiente visualización, el valor de la media móvil previa en esta última versión es normalmente inferior al calculado con la versión en la que utilizamos la función AVERAGEX, ya que el denominar será mayor cuando en el horizonte temporal considerado existan días en los que las ventas son iguales a cero.
Media móvil acumulativa
En ocasiones, el usuario desea obtener el promedio de todos los datos históricos existentes hasta el momento actual. Por ejemplo, un inversor puede estar interesado en conocer la media del precio de compra/venta de todas las transacciones para un determinado valor hasta el momento presente. A medida que se produce cada nueva transacción, el precio medio en el momento de la misma puede calcularse, para todas las transacciones hasta ese momento, utilizando el promedio móvil acumulativo o cumulative moving average (CMA), que suele ser una media igualmente ponderada de la secuencia de los n valores existentes en el conjunto de datos. Podemos resolver este escenario de forma muy fácil en DAX con el siguiente código:
MM Acumulativa =VAR _PeriodoSeleccionado =
FILTER (
ALLSELECTED ( Calendario[Fecha] );
Calendario[Fecha] <= MAX ( Calendario[Fecha] )
)
RETURN
AVERAGEX ( _PeriodoSeleccionado; CALCULATE ( SUM ( Criptos[Precio] ) ) )
En este escenario usamos la función ALLSELECTED para utilizar como límite inferior del horizonte temporal sobre el que calcular el promedio del precio, el seleccionado por el usuario en el segmentador, ignorando el filtro introducido por el eje de la visualización en la que se representan las medidas. Si en cambio utilizáramos la función ALL ignoraríamos por completo cualquier filtro temporal y la media se calcularía teniendo en cuenta el horizonte desde la primera fecha en la que disponemos de datos registrados para el precio del valor seleccionado, en este ejemplo el 1 de diciembre de 2014. En la siguiente imagen podemos ver la representación de ambas medidas:
Medias móviles mediante el uso de funciones de inteligencia de tiempo.
Si nuestro modelo de datos no contiene ninguna de las peculiaridades que nos impiden el uso de las funciones de Time Intelligence podemos recurrir a ellas con el objetivo de simplificar el cálculo de métricas de componente temporal, como es el caso que nos ocupa, las medias móviles.
Probablemente la forma más intuitiva de realizar este cálculo es mediante el uso de la función DATESINPERIOD(), que podemos utilizar como argumento de filtro de la función CALCULATE() y nos devuelve, en nuestra dimensión temporal, el conjunto de fechas comprendidas entre el periodo que especifiquemos en sus argumentos. Si por ejemplo queremos computar una media móvil a nivel mensual de las ventas podemos usar el siguiente código:
VAR _Periodo =
DATESINPERIOD ( Calendario[Fecha]; MAX ( Calendario[Fecha] ); -6; MONTH )
RETURN
IF (
[Ventas] > 0;
CALCULATE (
DIVIDE ( [Ventas]; DISTINCTCOUNT ( Calendario[MesDelAño] ) );
_Periodo
)
)
Podríamos también usar DATESBETWEEN() junto con otras funciones de Inteligencia de Tiempo para obtener el mismo resultado:
VAR _Periodo =
DATESBETWEEN (
Calendario[Fecha];
NEXTDAY ( DATEADD ( Calendario[Fecha]; -6; MONTH ) );
LASTDATE ( Calendario[Fecha] )
)
RETURN
IF (
[Ventas] > 0;
CALCULATE (
DIVIDE ( [Ventas]; DISTINCTCOUNT ( Calendario[MesDelAño] ) );
_Periodo
)
)
Con ambas medidas obtenemos el mismo comportamiento en una visualización:
Medias móviles estáticas con columnas calculadas
Otra de las formas en las que podemos implementar medias móviles es mediante la materialización de las mismas dentro del modelo de datos como columnas calculadas. Aunque el uso de columnas calculadas sea, en la mayoría de las situaciones, desaconsejable, existen ciertos casos en los que pueden ayudar a mejorar el rendimiento de nuestros informes. En el caso de las medias móviles, dependiendo del modelo con el que estemos trabajando, es probable que DAX necesite computar cientos de valores como promedios a lo largo de un gran conjunto de datos, operación que puede resultar bastante cara en términos de rendimiento y es probable que queramos consolidar el cálculo en una columna calculada para generar los gráficos de una manera más rápida y ofrecer una mejor experiencia al usuario.
Hay que tener en cuenta que estas columnas consumirán RAM y memoria en disco, haciendo el modelo más pesado, pero el objetivo de este artículo, como ya hemos comentado, es el de practicar el lenguaje DAX, no el de construir patrones definidos listos para usar en un modelo de datos propio.
Aunque para los ejemplos que siguen utilizo la cotización de criptomonedas, que se compran y venden todos los días, es probable que la tabla con la que estemos trabajando no contenga datos para cada uno de los días existentes en el horizonte temporal a analizar. Si queremos que los cálculos tomen en cuenta exactamente el número de periodos especificado por nosotros de manera que cada uno de los puntos corresponda a un día en el que la criptomoneda a analizar tenga un precio en nuestra base de datos, un primer paso corresponde a asignar un número correlativo para cada una de las criptomonedas que indique el número de observaciones de las que disponemos en cada momento para cada una de ellas. Para realizar dicho paso mediante una columna calculada podemos usar el siguiente código:
NumObservacion =VAR _CriptoActual = Criptos[Nombre]
VAR _FechaActual = Criptos[Fecha]
RETURN
COUNTROWS (
FILTER (
Criptos;
AND ( Criptos[Nombre] = _CriptoActual; Criptos[Fecha] <= _FechaActual )
)
)
El resultado sería el siguiente:
Con esta columna cada registro tiene un índice, que usaremos para establecer los extremos de las medias móviles a calcular, almacenando su valor en la variable _ObservacionActual.
Una vez obtenido este valor podemos calcular las medias móviles para cada registro de la tabla mediante diferentes fórmulas. Algunas de ellas, para un valor de n de 50 periodos, son las siguientes:
VAR _ObservacionActual = Criptos[ObservacionNum]
VAR _CriptoActual = Criptos[Nombre]
RETURN
AVERAGEX (
FILTER (
Criptos;
Criptos[ObservacionNum] <= _ObservacionActual
&& Criptos[ObservacionNum] >= _ObservacionActual – 50
&& Criptos[Nombre] = _CriptoActual
);
Criptos[Precio]
)
VAR _ObservacionActual = Criptos[ObservacionNum]
VAR _CriptoActual = Criptos[Nombre]
VAR _FechaActual = Criptos[Fecha]
RETURN
CALCULATE (
AVERAGE ( Criptos[Precio] );
FILTER (
ALL ( Criptos[Fecha] );
AND (
Criptos[Fecha]
>= LOOKUPVALUE (
Criptos[Fecha];
Criptos[Nombre]; _CriptoActual;
Criptos[ObservacionNum]; _ObservacionActual – 50
);
Criptos[Fecha] <= _FechaActual
)
);
ALLEXCEPT ( Criptos; Criptos[Nombre] )
)
VAR _FechaActual = Criptos[Fecha]
RETURN
CALCULATE (
AVERAGE ( Criptos[Precio] );
DATESBETWEEN ( Criptos[Fecha]; _FechaActual – 50; _FechaActual );
ALLEXCEPT ( Criptos; Criptos[Nombre] )
)
VAR _ObservacionActual = Criptos[ObservacionNum]
RETURN
CALCULATE (
CALCULATE (
AVERAGE ( Criptos[Precio] );
FILTER (
ALL ( Criptos[ObservacionNum] );
AND (
Criptos[ObservacionNum] <= _ObservacionActual;
Criptos[ObservacionNum] >= _ObservacionActual – 50
)
)
);
ALLEXCEPT ( Criptos; Criptos[Nombre] )
)
CALCULATE (
CALCULATE (
AVERAGE ( Criptos[Precio] );
Criptos[ObservacionNum] <= VALUES ( Criptos[ObservacionNum] );
Criptos[ObservacionNum]
>= VALUES ( Criptos[ObservacionNum] ) – 50
);
ALLEXCEPT ( Criptos; Criptos[ObservacionNum]; Criptos[Nombre] )
)
Como podemos observar en la siguiente imagen, todas las fórmulas anteriores nos devuelven el mismo resultado, y puede ser un buen ejercicio el intentar entenderlas todas:
Si calculamos también una media móvil con un número de periodos n = 200 y representamos en un gráfico de líneas los datos originales junto con las medias móviles de ambos periodos obtendremos el siguiente resultado:
Consideraciones finales
En este artículo hemos visto distintas formas de implementar una media móvil simple en Power BI mediante el uso del lenguaje DAX. La técnica básica consiste en expandir el contexto de filtro mediante el uso de argumentos de filtro de la función CALCULATE() para dejar visible el conjunto de fechas que nos interese en nuestra dimensión temporal.
En un artículo posterior iremos un poco más allá y aprenderemos a implementar medias móviles ponderadas y exponenciales que nos ofrecerán la posibilidad de introducir diversos factores de ponderación, con el objetivo de disponer de la flexibilidad que nos ofrece el hecho de poder asignar un mayor o un menor peso a los datos en función de su antigüedad.
Todo el código DAX de este artículo ha sido formateado con «DAX Formatter»
Deja un comentario