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

jueves, enero 02, 2020

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.


jueves, octubre 24, 2019

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