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:
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:
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:
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:
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:
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:
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.
