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

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.

Totales acumulados con Power Query

No me considero un profesional experto en Excel y aledaños (Power Query, PowerPivot), pero si un usuario experimentado. Por ese motivo las notas de este blog reflejan más mi trabajo cotidiano con esta fantástica herramienta que un enfoque técnico-profesional.

Una vez dicho ésto, vayamos al punto de la nota: en algún momento de nuestra vida tuvimos o tendremos que calcular totales o saldos acumulados ("running totals" en inglés).

En Excel Clásico (Excel Clásico = Excel sin Power Query y PowerPivot) sabemos hacerlo con fórmulas o, mejor aún, con tablas dinámicas. ¿Por qué, entonces, hacerlo con Power Query? Porque puede ser parte de una serie de transformaciones que estamos haciendo dentro de la consulta o porque, sencillamente, nos resulte más eficiente.

Sea cual sea el motivo vamos a ver cómo hacerlo en el entorno del Power Query.

Las trampas ocultas de Power Query

Confieso que el título de este post peca de sensacionalismo, pero es por una buena causa: mostrar que, si no prestamos la debida atención, las consultas que creamos con Power Query pueden fallar y cuando lo hacen, lo harán en el momento menos adecuado (por ejemplo cuando estamos demostrando las bondades del modelo que acabamos de construir a nuestro jefe o cliente).

Voy a mostrar a una de esas trampas: la trampa de dividir por columnas (basado en un hecho real 😊).

Esta tabla muestra en que países han realizados ventas algunos de nuestros agentes

Podemos ver que Steven a realizado ventas en 4 países , Michael en 5 y Anne también en 5.

Queremos organizar la tabla de manera que en la columna "País" aparezca un solo país por fila. Así tendremos 4 filas para Steven, 5 para Michael y 5 para Anna.

Damos los siguientes pasos:

Fechas anteriores al 1900 - versión Power Query

Como todos sabemos según Excel la historia comenzó el 1 de enero de 1900,. Es decir, Excel nos permite hacer cálculos de fechas sólo a partir de esa fecha. Cualquier intento de ingresar en una celda o hacer cálculos con fechas anteriores resultará en un error.
Hace once años publiqué un post con los posibles remedios a operaciones con fechas anteriores al 01/01/1900. Estas soluciones implican usar macros, como este que ofrece Microsoft para calcular edades con fechas anteriores al 1900, o instalar complementos.
¿Qué sucede en Power Query cuando queremos hacer cálculos con fechas anteriores al 1900? Supongamos esta tabla con las fechas de nacimiento y fallecimiento de hombres y mujeres de ciencia

En Excel las fechas anteriores al 1900 son interpretadas como texto y las posteriores como fechas (las posteriores al 1900 están alineadas a la derecha, como números, mientras que las anteriores a la izquierda).

Nuestra tarea es calcular las edades a las que fallecieron nuestros científicos. Vamos a ver que sucede cuando creamos una conexión a esta tabla en Power Query