Comparar dos periodos de manera dinámica en Excel

Normalmente, en Excel utilizamos tablas dinámicas para comparar una métrica en diferentes periodos. Por ejemplo, podemos querer ver los ingresos de enero de este año, comparándolos con los ingresos de octubre del año anterior. Para ello, tenemos que añadir el campo “mes” en los filtros de la tabla y el campo “año” en la misma tabla, en las filas o en las columnas.

 Cómo podemos ver en la imagen, de esta forma podemos comparar dos periodos y, además, podemos utilizar la función de las tablas dinámicas “Mostrar valores como”, eligiendo “% de” y seleccionando la comparativa con el año anterior. Esta funcionalidad está bastante bien, pero y ¿si quisiera compara enero 2022 con marzo 2021? En este caso deberíamos crear un campo de año-mes, insertarlo en la tabla y filtrar los dos periodos. ¿Y si quisiera comparar periodos que no sean meses completos? ¿Y si los periodos que quiero comparar se solapan?

Además, para un usuario final, lo ideal sería no tener que cambiar filas y columnas de la tabla dinámica, sino simplemente seleccionar los dos periodos de comparativa. Pues para ello hay una solución sencilla y una más compleja, ambas utilizando Power Pivot (si no conoces Power Pivot echa un vistazo a este post). La solución más compleja consiste en crear en Power Pivot dos tablas con los campos de fecha que necesitemos. Cada tabla hace referencia a un periodo concreto y se utilizarán para los filtros. El segundo paso sería crear campos calculados en Power Pivot que sumen las métricas deseadas y que se filtren por el valor seleccionado en las tablas de fechas. Esto puede complicarse más y podríamos tener que utilizar 4 tablas de fechas para elegir el principio y fin de los periodos 1 y 2.

Pero en este post os voy a hablar de un método mucho más sencillo y que he utilizado para crear una plantilla Excel de análisis de Precio Volumen Mix. No voy a entrar en el detalle de este análisis, pero el objetivo era que el usuario no tuviera que tocar las tablas dinámicas, sino que simplemente tuviera que elegir los dos periodos de comparación en los filtros. Al cambiar los valores en los filtros, las tablas mostrarían los ingresos del periodo 1 comparados con los ingresos del periodo 2, además de su variación en valor absoluto. Esto se puede expandir a otras métricas y otros cálculos, como la variación porcentual.

La solución sencilla para conseguir este resultado fue duplicar la tabla de datos. Como puedes ver en la siguiente imagen, el esquema de Power Pivot tiene una Tabla1 y una Tabla2 que son iguales (me refiero a las columnas, hablaremos luego de los campos calculados añadidos en la Tabla1). Los filtros de los dos periodos de tiempo proceden de cada una de estas tablas y, al seleccionar cada periodo, estaremos filtrando las filas de cada una de las tablas.

Por otro lado, necesitamos que el resto campos sean comunes así debemos crear lo siguiente:

• Tablas de dimensiones intermedias que crucen con las dos tablas de hechos (Tabla1 y Tabla2). Deberemos crear tantas tablas cuantos sean los grupos de dimensiones (podemos agrupar los campos jerárquicos)
• Crear campos calculados en una tabla cualquiera donde sumamos las métricas de la Tabla1 y Tabla2.

En realidad, la creación de campos calculados de ingresos de cada periodo no sería necesaria, dado que podemos utilizar las mismas columnas de cada una de las tablas, pero si que es más útil para el usuario dado que podrá disponer de todos los campos en el mismo conjunto de datos de la tabla dinámica. Además, para el cálculo de la comparativa no podemos usar las columnas de las tablas, sino que tenemos que usar un campo calculado ya que en este podemos llamar a campos de diferentes tablas, los cuales serán filtrados usando los periodos y las tablas intermedias. De esta forma el usuario podrá disponer de unos filtros dinámicos donde elegir los periodos de comparación y ver los resultados directamente en la tabla dinámica.

Si quieres ver los cálculos exactos de estos campos calculados, además de la tabla dinámica del ejemplo, puedes descargar la plantilla Excel de este ejemplo. Sólo necesitas suscribirte al blog.