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.



Para poder usar las bondades de PowerPivot tenemos que cargar la tabla al modelo de datos. Ésto podemos hacerlo de varias maneras. Dado que la columna fecha no contiene fechas reales sino (2015-01-01 es un texto, no una fecha), vamos a usar Power Query para hacer la transformación a fechas y cargar la tabla al modelo de datos. Usamos Datos- Obtener Datos-desde una tabla...y vemos que Power Query realiza al transformación en forma automática

Al apretar Cerrar y Guardar usamos las opciones Solo crear conexión y marcamos Agregar a modelo de datos

Ahora podemos ir a la ventana del modelo de datos para ver el resultado

Para poder usar las funciones de inteligencia de tiempo, necesitamos que el modelo incluya una tabla de fechas. Ésta es una tabla que contiene una serie de valores únicos para las fechas comprendidas en el período que queremos analizar.
Podemos crear esta tabla en Excel y cargarla al modelo de datos, pero también existe la posibilidad que PowerPivot nos ofrezca hacerlo en forma automática. En la ventana del modelo de datos activamos el menú Diseñar-Calendarios-Tablas de Fechas-Nuevo

PowerPivot crea automáticamente una tabla de fechas con varios campos: Fecha, Año, Número de mes, Nombre del mes, etc. A los efectos de este ejemplo he dejado solamente Fechas, Año y Número de mes

A diferencia de la tabla de datos, este tabla "calendario" contiene todas las fechas desde el 01/01/2015 hasta el 31/12/2018.

En la ventana del modelo de pasamos al modo de vista de diagrama donde podemos ver las dos tablas de nuestro modelos.

El próximo paso consiste en conectar ambas tablas, es decir, crear una relación entre ellas. El campo en común es "Fecha" en la tabla de datos y "Date" en Calendario. Creamos la relación apuntando al campo "Date" y arrastrando el mouse para apuntar a "Fecha"

El "1" en la tabla Calendario nos índica que esta es la Lookup Table y la estrella en Tabla1 que ésta es la tabla de datos (en nuestro ejemplo ambas tablas tienen valores únicos, pero por lo general las tablas de datos tendrán valores múltiples, es decir, más de un Monto para cada fecha).

Volvemos a la la hoja de Excel y usamos Insertar-Tabla dinámica para crear nuestro informe. Excel nos ofrece usar el modelo de datos para crear la tabla

Usamos el menú PowerPivot-Cálculos-Medidas  para crear la medida "Total de Ventas" con esta fórmula DAX: =SUM(Tabla1[Monto])

All apretar Aceptar la nueva nedida aparece en el campo de los valores de la tabla dinámica

Ahora vamos a arrastrar los campo Año y Mes de la tabla Calendario, no de la tabla de datos

Ahora creamos una nueva medida, TAM, basándonos en la medida Total de Ventas

=CALCULATE([Total de Ventas],
DATESBETWEEN( 'Calendar'[Date],
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date]))),
LASTDATE('Calendar'[Date])
)

)

con este resultado

En el año 2015 vemos solo el acumulado mensual ya que no existen datos del año anterior. El TAM en la fila 19 es el total de los doce meses de febrero 2015 a enero 2016; lo mismo en la fila 20, el total de los doce meses de marzo 2015 a febrero 2016 y así sucesivamente.

Explico la fórmula que ha creado la medida TAM:

• CALCULATE me permite aplicar filtros a la medida [Total de Ventas];
• el filtro lo creamos con DATESBETWEEN, para definir los 12 meses a tomar en cuenta en la medida.
• NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])) define la fecha inicial que es el día siguiente a la última fecha del mismo período del año anterior;
• LASTDATE('Calendar'[Date]) hace lo que su nombre en inglés dice.

Como toda medida, también ésta está sujeta al filtro del contexto. Es decir, es evaluada de acuerdo a la situación en la tabla dinámica (año y mes). De esa manera obtenemos los valores correctos para fila de la tabla dinámica.

Podemos crear una nueva medida [TAM promedio] con la sencilla expresión =[TAM]/12

Podemos ocultar el año 2015 filtrando la tabla dinámica.

Soluciones más elegantes en este post.