The Three-Statement Model in Power BI: Income Statement, Balance Sheet and Cash Flow

Income statement, balance sheet and cash flow reports built from the general ledger in Power BI

Most finance dashboards in Power BI stop at the Income Statement. Revenue, costs and profit are easy to show. The Balance Sheet and the Cash Flow Statement are where many teams go back to Excel.

They don't have to. With the right model, all three statements come from the same general ledger data, update together on every refresh, and tie to each other automatically. In this article I'll explain the structure I use and the DAX behind each statement.

Why build all three in Power BI

When the three statements live in one model:

  • They always agree. Profit for the period flows into retained earnings, and the change in cash matches the Cash Flow Statement
  • Month-end reporting is faster. No copying numbers between workbooks
  • Drill-down is possible. From a Balance Sheet line straight to the accounts and transactions behind it
  • Every slicer works on all three. Entity, period or business unit, in one click

Step 1: Start with clean ledger data

You need two things:

A general ledger or trial balance table with date (or period), account, entity and amount. Use one sign convention throughout: debits positive and credits negative is the most common.

A chart of accounts mapping table. This is the heart of the model. For every account, it holds:

  • Statement: Income Statement or Balance Sheet
  • Report line: for example Revenue, Cost of Sales, Trade Receivables, Inventory
  • Sort order: so lines appear in the right sequence
  • Cash flow category: for example Operating (working capital), Non-cash adjustment, Investing, Financing, or Cash

Spend time on this table with the accounting team. If the mapping is right, the DAX is simple. If it's wrong, no formula will fix it.

Step 2: The Income Statement

The Income Statement shows activity within a period, so a simple sum is enough. Because revenue is stored as a credit (negative), flip the sign for presentation:

DAX
GL Amount = SUM ( GL[Amount] )

Income Statement =
CALCULATE ( - [GL Amount], Account[Statement] = "Income Statement" )

Revenue now shows as positive and expenses as negative, so subtotals such as Gross Profit and Net Profit are simple sums of the lines above them.

Step 3: The Balance Sheet

The Balance Sheet shows balances at a point in time. A balance is the total of all movements from the beginning up to the selected date, so the measure has to ignore the start of the selected period:

DAX
Balance Sheet =
VAR LastDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [GL Amount],
        Account[Statement] = "Balance Sheet",
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= LastDate
    )

If your data starts from opening balances rather than from the first day of the business, load those opening balances as a transaction on the first date. The measure then works for every period after it.

The retained earnings line

On its own, this Balance Sheet won't balance. The profit of each period sits in Income Statement accounts until the year is closed. Add a line that brings accumulated profit into equity:

DAX
Retained Earnings =
VAR LastDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [GL Amount],
        Account[Statement] = "Income Statement",
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= LastDate
    )

The balance check

Always add a check measure. The total of all accounts, Balance Sheet and Income Statement together, must be zero at any date:

DAX
Balance Check =
VAR LastDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( [GL Amount], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= LastDate )

If this isn't zero, the issue is in the data, usually a missing opening balance or an unbalanced journal, not in the report.

Step 4: The Cash Flow Statement (indirect method)

This is the part most people find difficult, but the logic is straightforward. The change in cash equals the change in everything else, with the opposite sign. If receivables go up, cash went down. If payables go up, cash was kept.

So each Cash Flow line is simply the movement in the period for its mapped accounts, with the sign flipped:

DAX
Cash Flow =
CALCULATE (
    - [GL Amount],
    Account[Cash Flow Category] <> "Cash"
)

With the mapping table doing the grouping:

  • Net profit: all Income Statement accounts (depreciation included)
  • Non-cash adjustments: accumulated depreciation and provisions, which add the non-cash costs back
  • Working capital: receivables, inventory, payables and other current items
  • Investing: fixed asset additions and disposals
  • Financing: loans, equity and dividends

And the check that proves it works:

DAX
Cash Flow Check =
[Cash Flow] - CALCULATE ( [GL Amount], Account[Cash Flow Category] = "Cash" )

The total of the Cash Flow Statement must equal the movement in the cash and bank accounts, so this measure should always return zero.

Step 5: Present it like a finance team expects

  • Use the mapping table's report lines and sort order in a matrix, so the layout matches your management accounts
  • Add subtotal lines such as Gross Profit, EBITDA, Total Assets and Net Cash from Operating Activities, using a small layout table or visual calculations
  • Show comparisons: current period vs last period for the Income Statement and Cash Flow, and closing vs opening balances for the Balance Sheet
  • Put the checks on a hidden validation page, and look at it after every refresh

Final thoughts

A three-statement model in Power BI is one of the most valuable things a finance team can have. It replaces hours of month-end work, ensures the statements always agree, and lets management drill from a headline number to the transactions behind it in seconds. The key is a well-designed account mapping and a few careful DAX patterns.

Tecrionix delivers financial reporting solutions like this, from the data model to the final management pack. Explore our BI consulting services, or contact us to discuss your reporting requirements.

Looking to strengthen your Power BI reporting?

We build dashboards and train teams to use Power BI with confidence.