The data for this this dashboard is visualised in powerbi showing the finance report for a tech company. The dashboard is separated into 4 different tabs:
1. Overview: Main kpi’s for financial performance of their balance sheet and profit loss accounts.
2. Paid & Received
3. Outstanding
4. Statement
This is a monthly finance pack rather than a sales dashboard, so it has to satisfy two audiences at once: a finance team that wants the statement to tie out, and a management team that wants to know what changed since last month and who owes what.
The report is organised as four pages that follow the order those questions get asked in. Overview carries the headline position. Paid & Received covers cash that has actually moved. Outstanding covers what has not. Income Statement is the formal P&L, presented so it can be read the way an accountant reads one.
A straightforward star schema, which is the right answer for finance reporting because almost every question is "this amount, sliced by that dimension, for this period".
| Table | Role |
|---|---|
| fP<ransactions | The fact table. One row per accounting entry, carrying the amount, the control account, the entry status and the dates that drive both ageing and recognition. |
| dChartofAccounts | Account dimension. Holds AccountName and the statement hierarchy, so the P&L can be laid out in the order a statement is read rather than alphabetically. |
| dHeaderAccount | The grouping level above the chart of accounts, giving the statement its subtotal structure. |
| dBranches / dCompany | Organisational slicing: BranchName, City, StateCode, CompanyName. |
| dDate | Marked as the date table, driving every time comparison in the model. |
The important design decision is dHeaderAccount and the statement level columns. Without them, an income statement in Power BI turns into a fight with visual sorting. With them, the statement rows are data, not formatting.
There are 48 measures in the model, which sounds like a lot until you see how they group. Only a handful compute anything; the rest exist to make the first handful presentable.
Total Paid, Total Received, Payable Outstanding, Receivable Outstanding, Total Net Op Revenue, Total Net Profit, EBITDA.
Last-month and prior-year variants of each base measure, plus the increment measures that carry the delta.
Vertical Analysis (VA) and Horizontal Analysis (HA) for common-size and period-on-period statement reading.
Avg Days to Credit, Avg Days to Debit, Late Invoices Payable, Late Invoices Receivable.
Top 1 Paid Account Name and %, Top 1 Outstanding Receivable Name and %, answering "how exposed are we to one counterparty".
Icon and icon-colour measures for each KPI, plus Title Month and the smart narrative text.
On the DAX below: table and column names are taken from the model itself. The amount field is written as [Amount] for readability. These are the measure definitions as the model computes them, not a paste of the formula bar.
Paid and received are the same shape, separated by the control account. Keeping the filter in the measure rather than in the visual means the page can be sliced by branch and month without the definition drifting.
Total Paid =
CALCULATE(
SUM( 'fP<ransactions'[Amount] ),
'fP<ransactions'[ControlAccount] = "Payable",
'fP<ransactions'[EntryStatus] = "Paid"
)
Total Paid Last Month =
CALCULATE( [Total Paid], DATEADD( dDate[Date], -1, MONTH ) )
Total Paid Month Increment U$ = [Total Paid] - [Total Paid Last Month]
Outstanding is everything not yet settled, and the ageing measures are what turn it from a number into a collections conversation.
Payable Outstanding =
CALCULATE(
SUM( 'fP<ransactions'[Amount] ),
'fP<ransactions'[ControlAccount] = "Payable",
'fP<ransactions'[EntryStatus] <> "Paid"
)
Avg Days to Credit =
AVERAGEX(
FILTER(
'fP<ransactions',
'fP<ransactions'[ControlAccount] = "Receivable"
&& NOT ISBLANK( 'fP<ransactions'[SettledDate] )
),
DATEDIFF( 'fP<ransactions'[InvoiceDate], 'fP<ransactions'[SettledDate], DAY )
)
Late Invoices Receivable =
CALCULATE(
DISTINCTCOUNT( 'fP<ransactions'[InvoiceID] ),
'fP<ransactions'[ControlAccount] = "Receivable",
'fP<ransactions'[EntryStatus] <> "Paid",
'fP<ransactions'[DueDate] < TODAY()
)
Vertical analysis expresses every line as a percentage of revenue, so a small company and a large one can be compared on the same chart. Horizontal analysis does the same across time. Both need the denominator computed with the account filter removed, which is the part people usually get wrong.
Vertical Analysis (VA) =
DIVIDE(
[Result],
CALCULATE( [Total Net Op Revenue], REMOVEFILTERS( dChartofAccounts ) )
)
Horizontal Analysis (HA) =
VAR Current = [Result]
VAR Previous = CALCULATE( [Result], DATEADD( dDate[Date], -1, YEAR ) )
RETURN DIVIDE( Current - Previous, ABS( Previous ) )
A single measure that names the largest account, which is more useful on a card than another bar chart.
Top 1 Paid Account Name =
VAR TopAcc =
TOPN( 1, VALUES( dChartofAccounts[AccountName] ), [Total Paid], DESC )
RETURN CONCATENATEX( TopAcc, dChartofAccounts[AccountName] )
Top 1 Paid Account % =
DIVIDE(
CALCULATE( [Total Paid],
KEEPFILTERS( TOPN( 1, VALUES( dChartofAccounts[AccountName] ), [Total Paid], DESC ) ) ),
CALCULATE( [Total Paid], REMOVEFILTERS( dChartofAccounts ) )
)
The headline position: net operating revenue, net profit, net profit margin and EBITDA, each with its prior-year comparison and an icon showing direction of travel. The icons are measures rather than conditional formatting rules, which means the threshold logic lives in one place and can be reused on any visual.
Cash that has moved, split by direction, with invoice counts alongside values. Counts matter here: a large paid total driven by one invoice is a different situation from the same total across two hundred, and the Top 1 Paid Account % measure quantifies exactly that.
The working capital page. Payable and receivable outstanding side by side, late invoice counts, and average days to credit and debit. Read together these answer whether the business is being paid faster than it is paying, which is usually the question behind the question.
The formal statement, laid out by the account hierarchy, with vertical and horizontal analysis beside the values. Slicers for branch, year, month and transaction status sit above it, so the same statement can be pulled for any cut without building a second report.
Four of the eight pages in the file are tooltip pages, one each for paid, received, payables outstanding and receivables outstanding. Hovering a bar gives the breakdown for that bar without leaving the page. It removes a lot of drill-down clicking, and it keeps the main pages uncluttered because the detail has somewhere else to live.
Two things. The ageing measures answer "how late, on average" but not "how much sits in each bucket", so a proper 30/60/90 ageing band built as a calculated dimension rather than a set of measures would make the Outstanding page more actionable. And the model recognises transactions by entry date; an explicit accounting period key would let the pack handle prior period adjustments cleanly instead of quietly restating history.