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

Total Móvil Anual (TAM) con PowerPivot

Como ya he declarado alguna vez en el pasado, lo prometido es deuda. A un poco más de dos meses de haber publicado el segundo post sobre como calcular el total anual móvil donde prometí mostar como calcularlo con PowerPivot, paso a saldar mi deuda (mis pocos pero buenos lectores sabrán disculpar).

PowerPivot es imbatible en el área de cálculos que impliquen comparación de períodos (lo que en inglés se da por llamar "Time Intelligence"). Y lo vamos a mostrar con el tema que estamos tratando, el cálculo del total anual móvil.

En este post parto de la suposición que el lector tiene un  conocimiento adecuado de PowerPivot y en particular del lenguaje DAX, y si no es así, espero que esta nota sirva de aliciente para impulsarlo a estudiar estas herramientas.

Vamos al grano. Para este ejemplo vamos a usar una tabla de datos diarios (ventas) del período 2015-2018.

Nuestra tarea es calcular el total anual móvil (TAM) para cada mes, es decir, totalizar el total mensual y compararlo con el acumulado de los últimos doce meses. Luego podemos calcular el promedio mensual del TAM y compararlo con las ventas del mes.

Total Anual Móvil (TAM) con Excel

A colación del post sobre totales acumulados me consulta un lector sobre la posibilidad de calcular el total anual móvil (TAM) con Power Query (y por mi cuenta voy a agregar como hacerlo con PowerPivot). Para ilustrar el tema ne envía un enlace a esta página del sitio SageAdvice de donde tomé esta tabla de datos

El Total Móvil Anual (TAM) es la suma de los doce últimos meses de lo que estamos midiendo, en nuestro caso las ventas. Así el TAM en enero incluye los meses Febrero a Diciembre del año anterior (2011 en la imagen arriba) y el mes de Enero del 2012. De la misma manera, Febrero suma los meses de Marzo a Diciembre del 2011 y Enero y Febrero del 2012, y así sucesivamente.

Ahora que sabemos qué es el TAM, vamos a ver como calcularlo con tres herramientas: Excel Clásico, Power Query y PowerPivot.