Total Móvil Anual (TAM) con Power Query

En el post anterior vimos como calcular el Total Anual Móvil usando fórmulas en Excel Clásico y prometimos mostrar como hacerlo con Power Query. Lo prometido es deuda, así que aquí va la explicación.

Empezamos por la misma tabla de datos de la nota anterior

Para calcular el TAM de Enero tenemos que sumar las ventas desde febrero del 2017 a enero del 2018 incluido, es decir los últimos doce meses. De la misma manera para febrero: desde marzo del 2017 a febrero del 2018 y así sucesivamente.

Veamos cómo creamos el informe con Power Query

Empezamos por crear una consulta a la tabla. Como es de (no tan buena) costumbre, Power Query crea automáticamente un paso aplicado cambiando el tipo de dato de cada columna según su mejor entendimiento. En nuestro caso las columnas de venta reciben el tipo de dato Número Entero (identificado por el símbolo 123 a la izquierda del encabezado de la columna). Esto puedo generar problemas ya que en otros casos los datos pueden ser números decimales. Así que cambiamos el tipo de datos a número decimal. Podemos hacerlo agregando un paso con la interfaz de usuario o editando el código y reemplazando Int64.Type por type number, que es lo que he hecho

En el próximo paso creamos dos columnas con números de índice: una de 1 al 12 y la otra de 12 a 1

Para crear la columna Índice.1 usamos la opción Agregar columna - Columna de índice - Personalizado con un índice inicial 12 y un incremento de -1

Al agregar los índices Power Query crea dos pasos aplicados con los nombres Índice agregado y Índice agregado.1. Apuntamos al paso y con el botón derecho del mouse abrimos el menú contextual; usamos la opción Cambiar nombre para cambiarlos a IndAgr e IndAgr2.  Los nombres pueden ser éstos o cualquier otro a condición de que no contengan espacios.
Este paso no es imprescindible pero nos facilitará el trabajo en los pasos siguiente.

Ahora agregamos una columna personalizada para calculara el total acumulado descendente de las ventas 2017 con la siguiente fórmula

Esta fórmula es similar a la que usamos en el post sobre totales acumulados. List.Sum suma los valores de la lista (serie de valores) determinada por la función List.LastN. Esta función tiene dos argumentos: la columna (lista) de la cual queremos extraer los valores, y el número de valores.

En la función List.LastN usamos la expresión [Índice.1]-1 para indicar cuantos elementos de la lista se deben tomar en cuenta.  Por eso el valor de al columna Acum2017 en la primera fila de la tabla es 79000 y no 80000 (es decir, no incluye el mes de enero 2017) y el valor para diciembre es null. Ésto es intencional.

Ahora vamos a calcular el total acumulado ascendente para las ventas del 2018 con esta fórmula

List.Sum(List.FirstN(acum2017[Ventas 2018],[Índice]))

acum2017 es el nombre rebautizado del paso anterior donde calculamos el total acumulado del 2017.

A esta altura de los acontecimientos ya no necesitamos las columnas de índices por lo que podemos eliminarlas. Nuestra consulta ahora se ve así

El último valor de la columna Acum2017 es null que vamos a reemplazar por 0 (no podemos hacer operaciones aritméticas que con valores null).
Si observamos las filas de las columnas vemos que en la primer fila Acum2017 suma once meses (no incluye enero) y Acum2018 un mes (enero 2018); en la segunda fila Acum2017 suma los últimos nueve meses del año y Acum2018 los dos primeros y así sucesivamente. Así que una vez reemplazado el valor null, sumando la columna Acum2017 a Acum2018 obtenemos el TAM

Finalmente podemos eliminar las columnas Acum2017 y Acum2018 y cargar la consulta a una hoja de Excel

En el próximo post veremos como hacerlo con PowerPivot.