Power Pivot para Finanzas

Power Pivot para Finanzas

En este tutorial completo de Power Pivot para FINANZAS, aprenderás a crear un modelo de datos sencillo en Excel que te permitirá trabajar con tablas dinámicas avanzadas basadas en el modelo de datos, y no solo con la función por defecto. Descubre cómo generar medidas en Power Pivot que incluyen cálculos complejos, fundamentales para sistematizar el reporting financiero en Excel. Este video es ideal para quienes buscan dominar el análisis financiero Power Pivot, con comparaciones mensuales Excel, acumulados Excel y reportes de año completo

Las tablas dinámicas nos sacan de muchos apuros, pero cuando el reto es montar un reporting financiero de verdad —datos del mes, acumulados, total año, comparativas contra presupuesto y año anterior— se quedan cortas. Ahí es donde entra Power Pivot y el modelo de datos. La buena noticia es que no hace falta dominar todo DAX ni construir un modelo perfecto: con un buen diseño del modelo y solo tres funciones de DAX, podemos resolver las necesidades financieras más habituales sin complicarnos la vida.

La idea: simplificar el modelo

El truco no está en formular cosas muy complejas, sino en cómo montamos el modelo para sacarle el mayor jugo posible. Para ello partimos de dos decisiones clave:

  • Trabajar a nivel mensual. En la práctica, el reporting financiero suele ser mensual, así que el calendario será una tabla muy especial: una simple lista de meses del 1 al 12, con su nombre. No es una dimensión de fechas completa, solo números enteros que representan el mes.
  • Una única tabla de hechos. En lugar de tener contabilidad por un lado y presupuesto por otro, lo unificamos todo en una sola tabla de hechos financieros. Una columna llamada escenario indica en cada línea si el dato es real del año actual (actual), del año anterior (prior year) o presupuesto (budget). Esto facilita enormemente la creación de las medidas.

Las tablas de dimensiones son dos: cuentas (cuenta, epígrafe y grupo, con su jerarquía) y calendario (los meses).

Preparar los datos con Power Query

Cargamos la tabla de contabilidad en Power Query y añadimos una columna condicional llamada escenario: si el año es 2025, el valor es actual; en caso contrario, prior year. Después limpiamos: dejamos solo mes, cuenta, centro, importe y escenario, y quitamos fecha, año, descripción y documento. Asignamos los tipos correctos:

  • mes → número entero (lo usaremos para vincular).
  • cuenta → texto (aunque parezca un número, la tratamos como texto).
  • centro → texto.
  • importe → número decimal.
  • escenario → texto.

Repetimos el proceso con el presupuesto, quedándonos con los mismos campos. Aquí el escenario lo creamos con una columna personalizada de valor fijo: budget.

Para juntar ambas consultas usamos Anexar. Recuerda que al anexar lo importante es el nombre de la columna, no el orden. El resultado es una tabla unificada de datos financieros donde cada línea queda marcada como actual, budget o prior year. Cargamos cada consulta auxiliar como solo conexión y la enviamos al modelo de datos, igual que las tablas de cuentas y calendario.

Crear las relaciones

En la pestaña Power Pivot → Administrar, vista de diagrama, creamos las relaciones: cuenta con cuenta, y mes del calendario con mes de los hechos. No hace falta marcar la tabla calendario como tabla de fechas: trabajamos con números enteros.

Las medidas DAX, paso a paso

Ya en la tabla dinámica (insertada desde el modelo de datos), montamos las filas con la jerarquía grupo → epígrafe y ponemos el mes como filtro. Al arrastrar el importe se crea una medida implícita. A partir de ahí, escribimos nuestras propias medidas.

1. Medida base. Una suma simple del importe:

  • saldo := SUM(contabilidad[importe])

2. Aislar cada escenario con CALCULATE. La función CALCULATE permite modificar el contexto de filtro. Creamos una medida por escenario que fuerza el valor sin importar la cabecera:

  • actual := CALCULATE(SUM(contabilidad[importe]); contabilidad[escenario] = "actual")
  • budget := CALCULATE(SUM(contabilidad[importe]); contabilidad[escenario] = "budget")
  • prior year := CALCULATE(SUM(contabilidad[importe]); contabilidad[escenario] = "prior year")

Así, cada medida respeta el filtro de cuenta, epígrafe y mes, pero ignora el escenario de la columna y muestra siempre el que hemos definido.

3. El mes seleccionado. Para los acumulados necesitamos saber qué mes ha elegido el usuario. La función SELECTEDVALUE lo resuelve:

  • mes seleccionado := SELECTEDVALUE(calendario[mes])

Devuelve un valor único solo cuando hay un único mes seleccionado.

4. Acumulados (Year To Date). Combinamos la medida del escenario con un filtro sobre el calendario menor o igual al mes seleccionado:

  • actual year to date := CALCULATE([actual]; calendario[mes] <= [mes seleccionado])

Reutilizamos la medida actual para no repetir el filtro del escenario. Hacemos lo mismo para budget year to date y prior year to date.

5. Full year. Para ver el total del año (útil sobre todo en budget y prior year), anulamos el filtro del mes con ALL:

  • budget full year := CALCULATE([budget]; ALL(calendario))
  • prior year full year := CALCULATE([prior year]; ALL(calendario))

Diferencias y formato

Con las medidas listas, las comparativas son una simple resta y una división:

  • diferencia budget mes := [actual] - [budget]
  • variación budget mes := DIVIDE([diferencia budget mes]; [budget]; 0)

DIVIDE evita errores cuando el denominador es cero (último argumento como alternativa). Por último, una ventaja muy práctica de las medidas: se les puede anclar un formato que se aplica automáticamente cada vez que se usan.

Conclusión

Con un modelo en estrella sencillo, una única tabla de hechos con la columna escenario y unas pocas funciones (CALCULATE, SELECTEDVALUE, ALL y DIVIDE), conseguimos una cuenta de resultados con dato del mes, acumulado y total año, además de las diferencias contra presupuesto y año anterior. La clave es invertir el esfuerzo en el diseño del modelo para mantener el DAX al mínimo y el reporting bajo control.

Más casos con estas funciones

Más contenido de Excel en InflueXcel