lunes, enero 06, 2020

Total Anual Móvil con PowerPivot - versión mejorada

En el post anterior mostré como calcular el TAM (Total Anual Móvil) con PowerPivot. La solución tiene algunos problemas:

  1. no muestra los totales para cada año;
  2. el cálculo del TAM promedio es inexacto para el primer año (2015 en nuestro ejemplo).
Aparentemente podemos solucionar el primer problema con facilidad; sencillamente agregamos los subtotales para el campo Year (el campo que arrastramos al área de las filas), pero entonces nos topamos con un nuevo problema


La tabla dinámica muestra los subtotales para todos los campos de valores y el total anual de TAM no tiene ningún sentido. Si estuviéramos trabajando sólo con tablas dinámicas no tendríamos como solucionar este problema (excepto con trucos de dudosa moralidad tales como cambiar el color de la fuente). Pero DAX nos permite solucionar este problema. Creamos la medida [TAM mejorado] de esta manera

=IF(HASONEVALUE('Calendar'[Month Number]),
                                       [TAM],
                                       BLANK()
                                       )



Cuando agregamos esta medida a la tabla dinámica veremos


La expresión HASONEVALUE('Calendar'[Month Number]) evalúa si la fila en la tabla es un total de mes; en caso afirmativo muestra la medida [TAM]; en caso negativo usa la función BLANK(). De esa manera, cuando se trata de un total del campo Year el valor mostrado es blanco (vacío).

Es hora de enfrentarnos con el segundo problema, ¿cómo calcular el TAM promedio en función de la cantidad de meses incluidos en el cálculo?.
Para hacerlo vamos a crear una medida que nos dé el número de meses en cada total de TAM. Nuestra medida debe hacer lo siguiente:

  1. si el año es 2015, mostrar el número de mes (esto coincide con ell número de meses tomados en cuenta en el cálculo del TAM);
  2. si no es 2015, mostrar 12.
Para facilitar la tarea vamos a crear dos medidas. La primera, [Meses_temp], da el número de mes que esta siendo evaluado en la fila de la tabla dinámica

[Meses_temp] = IF(HASONEVALUE('Calendar'[Month Number]),
                                VALUES('Calendar'[Month Number]),
                                 BLANK()
                                 )


Nuevamente usamos HASONEVALUE para evaluar si la fila es un subtotal del mes; en caso afirmativo usamos VALUES('Calendar'[Month Number] para mostrar el número de mes; en caso contrario un valor en blanco.
Agregamos la medida a la tabla dinámica para comprobar si el cálculo es correcto


Vemos que la medida funciona correctamente así que la quitamos de la tabla.
Ahora creamos otra medida [Meses_calc] que sencillamente evalúa si el año de la fila es 2015 o no; en caso de ser 2015, muestra el resultado de la medida [Meses_temp]; en caso contrario da el resultado 12

[Meses_calc] = IF(HASONEVALUE('Calendar'[Year]),
                              IF(VALUES('Calendar'[Year])=2015,
                                    [Meses_temp],
                                     12)
                                 )


Como puede apreciarse, usamos una medida dentro de otra medida. De esta manera simplificamos la sintaxis de la expresión.

Ahora creamos [TAM Promedio] con esta sencilla fórmula

[TAM Promedio]=[TAM mejorado]/[Meses_calc]



Ahora tenemos una medida útil para todos los datos, incluidos los del primer año.

3 comentarios:

  1. Ni que hablar mi estimado Jorge, super mejorado su proceso a base del puro estilo DAX.
    Saludos.👌👏

    ResponderBorrar
  2. Hola Jorge,
    Hay algo que no me cuadra. La TAM del primer mes del 2016 no debería ser la del diciembre del 2015 más las ventas de enero de 2016?
    Si te das cuenta parece ir al revés la TAM durante el año 2016.

    ResponderBorrar
    Respuestas
    1. Hola, me explico:
      1 - El TAM muestra el acumulado de los últimos 12 meses, por eso el primer mes del 2016 es febrero del 2015.
      2 - Parece pero no lo es. Sucede que cada TAM es el acumulado de los últimos 12 meses (como explico en el punto anterior) y nuestra imaginaria empresa está vendiendo, lamentablemente, cada vez menos (esa es la utilidad de esta medida)

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.