Automatically plug or balance your balance sheet in Power BI

This article presents a method to automatically plug or balance your balance sheet in Power BI. You can use it if your bookkeeping system doesn’t contain the Profit and Loss (PnL) bookings yet. Or for planning or financial modelling purposes when your calculated balance sheet figures don’t add up.

The problem to plug or balance your balance sheet

If you are not familiar with the problem: In the double bookkeeping world, entries made to the system will always add up to zero. For example, if you are selling an item for 100 Euros and receiving these 100 Euros in cash for it, this transaction will be recorded with 100 in your cash account (Balance Sheet) and -100 in your sales account (Profit and Loss Statement). So 100 – 100 will add up to 0. But as these figures are showing up in 2 different statements (PnL and Balance Sheet), both of these statements would not be balanced (to 0). Typically, they only balance at year-end when the PnL balance books against equity in the Balance Sheet.
But for reporting purposes during the year, you need a balanced Balance Sheet while the PnL Statement remains unbalanced, reflecting actual profit or loss. To achieve this, use a calculated figure to match the PnL balance or the inverse of the Balance Sheet sum.

A plug in financial modelling or for planning purposes technically comprises of the same elements: The forecasted balance sheet positions don’t add up and you need a figure in a balance sheet account to balance it out.

The DAX to balance

To begin with, let’s visualize the starting point:

 

With a turnover of 100 and costs of 60 our PnL shows a profit of 40 and the balance sheet reflects this by being off-balance by the same (absolute) amount.
But for reporting purposes, we want to show the periodic profit in separate position “Current Year Profit and Loss” like so:

Our Chart of Accounts table (CoA) looks like so:

I have included the desired balance sheet position in there (see last row). The column “Account” is part of my visual and for the basic DAX-part, I will first check if the current position is actually that account. If so, my measure should show the inverted sum of my balance sheet so far. And if not, just show the ordinary measure (“TotalAmount”). So a basic implementation could look like so:

MeasureWithPlug_Basic =
IF (
    MAX ( CoA[Account] ) = “Current Year Profit and Loss”,
    CALCULATE ( [TotalAmount], ALL ( CoA ), CoA[Statement] = “BalanceSheet” ) * -1,
    [TotalAmount]
)

To retrieve the total of the balance sheet, I have removed all filters from my CoA-table and reinstalled a filter on the BalanceSheet statement within the CALCULATE function.

However, this would not return correct subtotals:

Coming from Excel, you might guess this being due to circular dependencies. But not so in DAX: It is because the check for the “Current Year Profit and Loss”-account needs to be done on a row-by-row basis. So to fix this I will wrap a SUMX around the logic where I iterate over the Account-column:

MeasureWithPlug =
SUMX (
    VALUES ( CoA[Account] ),
    IF (
        CALCULATE ( MAX ( CoA[Account] ) ) = “Current Year Profit and Loss”,
        CALCULATE ( [TotalAmount], ALL ( CoA ), CoA[Statement] = “BalanceSheet” ) * -1,
        [TotalAmount]
    )
)

Please note that there is another CALCULATE around the MAX(CoA[Account]) for context transition.

Summary

With DAX you don’t need to worry about circular dependencies when calculating your plug or dynamically balance the balance sheet. However, you need to include context transition in your code.

Please also check the file enclosed if you want to examine further: Plug-or-balance-Balance-Sheet.pbix

Enjoy and stay queryious 😉