El representar correctamente el saldo acumulado del libro mayor de una cuenta contable es un problema muy común entre los que nos dedicamos al mundo financiero y estamos implementando nuestros modelos financieros con Power BI.
Solemos llegar a soluciones parciales, pero no conseguimos llegar al resultado que nos ofrece cualquier ERP de mostrarnos correctamente el saldo acumulado que tiene la cuenta contable después de cada uno de los apuntes, tal y como se muestra en la siguiente imagen:
Obviamente, la fórmula DAX a emplear dependerá de cómo tengas montado tu modelo de datos.
Para el ejemplo de este post el modelo de datos sería el siguiente:
Una tabla de hechos con los movimientos del libro diario, y las distintas dimensiones para filtrar la información.
La fórmula DAX que yo estaba usando para calcular el saldo acumulado del libro mayor era la siguiente:
Saldo Ac. Mayor =
IF (
[Saldo],
CALCULATE (
[Saldo],
ALLSELECTED ( dimApuntes ),
FILTER ( ALL ( dimFecha[Fecha] ), dimFecha[Fecha] <= MAX ( dimFecha[Fecha] ) )
),
BLANK ()
)
Esta fórmula es una adaptación de una aportación que hizo hace ya un par de años ( o algo más 😅) Leonardo Bergua dentro de la comunidad de Power Platform University.
Te muestro cómo queda en una hoja de detalle de un libro mayor:
Esta medida va acumulando correctamente el saldo acumulado pero tiene un principal inconveniente: si hay varios asientos en la misma fecha (como se destaca en los recuadros rojos y azules de la anterior imagen), la medida muestra el mismo valor para todos los registros de esa fecha (cuando lo esperado sería que diese a cada registro su saldo acumulado y no a todos el saldo resultante al final del día del contexto).
Cuando vi el siguiente vídeo del gran David Uyarra sobre la nueva funcionalidad de cálculos visuales aplicada a calcular saldos contables, enseguida pensé en probarlo para adaptar mi informe financiero y evitar el inconveniente citado anteriormente:
https://www.youtube.com/watch?v=kM0_sqF-fR4
Sin embargo, la funcionalidad del vídeo anterior se ejecuta correctamente cuando no filtramos fechas o, si filtramos fechas, necesitamos que el filtro no deje fuera del contexto el saldo de apertura para que los saldos se arrastren correctamente desde el movimiento de apertura.
Como en mis modelos yo me quedo únicamente con el saldo de apertura del año inicial, esta restricción era un gran escollo, ya que me serviría únicamente si no filtro ninguna fecha y tengo la totalidad de apuntes que afectan al libro mayor.
En una conversación que tuve con Diego Duque en el reciente evento de Power BI Days Bilbao, estuvimos hablando sobre esta problemática que él también estaba afrontando. Se nos fueron ocurriendo varias opciones que se iban cayendo porque no llegaban a resolver el problema😥
Pero una de las opciones, que combinaba cálculos DAX con los nuevos cálculos visuales, parecía que podía llegar a solucionar el requisito…estas conversaciones en comunidad sin duda que son lo mejor para desatascar problemas 😊
Hasta ahora no había sacado tiempo de ponerme a cacharrear. He podido probar la alternativa y me ha funcionado correctamente, así que voy a desarrollar a continuación los pasos seguidos por si a alguien más le es de utilidad:
1.- Partimos del saldo inicial de la cuenta contable reflejada en el libro mayor. Ese saldo inicial sería el saldo acumulado de la cuenta contable en el día justamente anterior al contexto seleccionado. Esa medida es la que se muestra en el recuadro sombreado en amarillo en la imagen 1 (19.808,05 €).
Esa medida de “Saldo Inicial” en mi modelo tiene el siguiente código DAX:
Saldo Inicial Libro Mayor =
IF ([Saldo],
//Para que solo pinte saldo inicial en caso de que esté en un asiento con saldo para el contexto filtrado
IF (
LEFT ( SELECTEDVALUE ( dimCuentasContables[Subcuenta contable] ), 1 )
IN { “6”, “7” },
//Si está en una cuenta de gasto o ingreso no quiero que suma al saldo inicial los saldos anteriores a la fecha mínima (si son cuentas de balance sí), sino que quiero que el saldo inicial sea cero y el saldo que pinte el libro mayor vaya sumando únicamente los importes del periodo seleccionado en el contexto.
0,
CALCULATE (
[Saldo],
FILTER (ALL ( dimFecha[Fecha] ), dimFecha[Fecha] < MIN ( dimFecha[Fecha] ) )
)),
BLANK ()
)
2.- Necesito construir una medida que repita ese valor en todas las filas del visual de tabla del libro mayor (imagen 1) y que pueda usar posteriormente en los cálculos visuales de esa tabla.
La medida que construyo es la siguiente:
//Calculo la fecha mínima del contexto seleccionado en los filtros externos a la tabla:
VAR FechaMinima =
CALCULATE ( MIN ( dimFecha[Fecha] ), ALLSELECTED ( dimFecha ) )
//Calculo el saldo inicial acumulando el saldo de las fechas que sean anteriores a la anterior variable:
VAR SaldoInicial =
IF (
[Saldo],
//Para que solo pinte saldo inicial en caso de que esté en un asiento con saldo para el contexto filtrado
IF (
LEFT ( SELECTEDVALUE ( dimCuentasContables[Subcuenta contable] ), 1 )
IN { “6”, “7” },
//Si está en una cuenta de gasto o ingreso no quiero que sume al saldo los saldos anteriores a la fecha mínima (si son cuentas de balance sí), sino que quiero que el saldo inicial sea cero y el saldo que pinte el libro mayor vaya sumando únicamente los importes del periodo seleccionado en el contexto.
0,
CALCULATE (
[Saldo],
ALLSELECTED ( dimApuntes ),
ALLSELECTED ( dimFecha ),
dimFecha[Fecha] < FechaMinima
)
),
BLANK ()
)
RETURN
SaldoInicial
Vemos cómo queda esa medida con el saldo inicial de 19.808,05 € repetido en cada una de las filas de la tabla:
3.- A continuación, hago un cálculo visual con el saldo acumulado, que hace un RUNNINGSUM de la medida “Saldo”, tal y como se explicaba en el vídeo de David Uyarra.
Recuerda que tienes que tener activada la opción de Cálculos visuales en las características de versión preliminar de las opciones de configuración de Power BI Desktop.
Te recomiendo revisar el vídeo de David Uyarra que mencioné anteriormente si tienes algún problema a la hora de crear este cálculo visual.
4.- Por último, hago un nuevo cálculo visual dentro del visual de tabla para sumar la medida “Saldo Inicial Libro Mayor Cálculo Visual” explicada en el punto 2 y el cálculo visual “SaldoAcumulado1” explicado en el punto 3:
Esta última medida sí que muestra correctamente el saldo del libro mayor para cada uno de los registros sin tener el inconveniente de que se repita el saldo en los apuntes de la misma fecha.
Ahora ya solo queda jugar con los formatos y dejar visibles únicamente las columnas que necesitemos mostrar para llegar a un resultado similar al que obtendríamos desde un ERP.
En la siguiente imagen muestro cómo quedaría para una cuenta de balance (arrastra el saldo anterior al periodo seleccionado en el contexto):
Y en esta otra imagen vemos cómo quedaría para una cuenta de PyG, donde no queremos que arrastre el saldo anterior sino que muestre el saldo para el periodo filtrado:
Espero que te pueda ser de utilidad a la hora de elaborar tus modelos financieros.
Cualquier aporte o mejora son bienvenidos, así que no dudes en contactarme!!!
PD: Gracias a Montse Collantes por su revisión del post, ya que en la primera versión que publiqué únicamente daba solución a las cuentas de balance. Con esta actual versión ya damos solución tanto a cuentas de balance como de PyG.