Categorías
DAX

Cálculo de media móvil con DAX

Las funciones de Inteligencia de Tiempo de Power BI son muy útiles para poder hacer comparaciones relativas a otros periodos en el tiempo. Con la ayuda de CALCULATE y SAMEPRIODLASTYEAR o DATEADD podemos obtener de manera rápida valores para algún día, mes, trimestre o año anterior o incluso posterior.

Pero no siempre nuestras comparaciones serán con respecto a un periodo en particular ya que podríamos querer eliminar ciertas fluctuaciones de corto plazo dadas por una estacionalidad para lo que necesitamos suavizar estas por medio de medias móviles. Esta calculo deja de ser tan sencillo, ya que requiere la combinación de más de una fórmula de inteligencia de tiempo para llegar al resultado deseado. Además, dependiendo de cómo estén organizados nuestros datos podríamos encontrarnos con distintos inconvenientes.

A continuación, veremos un ejemplo para poder calcular el promedio móvil de ventas de una cierta cantidad de meses y abordaremos algunas consideraciones a tener en cuenta.

Como suelo mencionar en mis clases cuando enseño sobre fórmulas DAX, antes de pensar en qué fórmulas utilizar, debemos pensar la lógica de el cálculo que queremos hacer.  Primero, debemos traer el monto total de las ventas que van desde el primer día del mes -x, y movernos los mismos x periodos hacia adelante. Por ejemplo, para abril del 2018 si tomamos 3 meses, debemos considerar el periodo que va desde el 1 de enero hasta el 31 de marzo del mismo año. Luego todo este monto, lo vamos a dividir por 3. Entonces vamos a desglosar este cálculo por parte:

VAR _Inicio = DATEADD(STARTOFMONTH('Dim.Tiempo'[Date]), -3, MONTH)

Acá definimos la fecha de inicio desde donde tomara los montos, es decir, 3 meses atrás del primer día del mes para el que estamos evaluando nuestra medida.

VAR _Inicio = DATEADD(STARTOFMONTH('Dim.Tiempo'[Date]), -3, MONTH)
VAR _Fechas = DATESINPERIOD('Dim.Tiempo'[Date], _Inicio, 3, MONTH)

En segundo lugar, definimos una nueva variable, que será la tabla que contenga las fechas entre nuestro _Inicio hasta 3 meses adelante. La fórmula DATESINPERIOD entrega una tabla con fechas que van desde la fecha de inicio indicada hasta x periodos hacia adelante/atrás.

Media Móvil 3M = 
VAR _Inicio = DATEADD(STARTOFMONTH('Dim.Tiempo'[Date]), -3, MONTH)
VAR _Fechas = DATESINPERIOD('Dim.Tiempo'[Date], _Inicio, 3, MONTH)

RETURN
CALCULATE(
    [Ventas],
   _Fechas
) / 3

Finalmente, creamos la medida con un CALCULATE, utilizando como filtro nuestra tabla _Fechas y lo dividimos por 3.

El resultado va a depender de los valores que tengamos en la tabla Calendario. Si generamos nuestra tabla calendario por medio de CALENDARAUTO, vamos a obtener una fecha de inicio en blanco para todas aquellas fechas en que DATEADD no encuentre una fecha. Ej.: Si nuestra tabla de hechos inicia el 01-01-2018 y para la fecha 01-02-2018 aplicamos la función DATEADD para movernos 2 periodos hacia atrás, entonces nos entregará como resultado blank.

Ahora hay que entender como se comportará la función DATESINPERIOD cuando le entregamos como valor inicial una fórmula que de como resultado blank. Según la documentación de Microsoft ocurre lo siguiente:  

La tabla devuelta solo puede incluir fechas almacenadas en la columna dates. Por ejemplo, si la columna dates comienza el 1 de julio de 2017 y el valor start_date es el 1 de julio de 2016, la tabla devuelta comenzará a partir del 1 de julio de 2017.

¿Cómo solucionamos entonces este problema? Podemos agregar una condición para que, por ejemplo, no nos entregue ningún valor si es que no existen datos desde al menos 3 meses atrás. Una opción sería la siguiente forma:

Media Móvil 3M = 
VAR _Inicio = DATEADD(STARTOFMONTH('Dim.Tiempo'[Date]), -3, MONTH)
VAR _Fechas = DATESINPERIOD('Dim.Tiempo'[Date], _Inicio, 3, MONTH)
VAR _MinFecha = MINX(ALL(Sales[Fecha]), Sales[Fecha])

RETURN
CALCULATE(
    [Ventas],
   _Fechas
) / 3

Primero, incluir una nueva variable que nos entregue la menor fecha en nuestra tabla de hechos.

Media Móvil 3M = 
VAR _Inicio = DATEADD(STARTOFMONTH('Dim.Tiempo'[Date]), -3, MONTH)
VAR _Fechas = DATESINPERIOD('Dim.Tiempo'[Date], _Inicio, 3, MONTH)
VAR _MinFecha = MINX(ALL(Sales[Fecha]), Sales[Fecha])

RETURN
IF(
    _Inicio < DATE( YEAR(_MinFecha), MONTH(_MinFecha), 01 ),
    BLANK(),
    CALCULATE(
        [Ventas],
    _Fechas
    ) / 3
)

Para terminar, agregamos una condición por medio de IF que nos entregue en blanco cuando la fecha de inicio sea menor al día 1 de esta _MinFecha.

Si quiere saber más sobre fórmulas DAX y aprender a hacer cálculos como este que puedan potenciar los análisis en tu negocio, consulta por nuestros cursos de capacitación acá.