How to Track Cash Flow in Power BI

Cash flow is the lifeblood of every small business. You can be profitable on paper and still run out of cash. A Power BI cash flow dashboard does not replace your accountant — but it gives you a daily picture of your position without waiting for monthly accounts.

This guide covers how to structure the data, which measures to build, and what the dashboard should show.

What data you need

A cash flow dashboard needs two things: a record of money coming in and a record of money going out. In most SMEs this comes from:

• Bank transaction exports (CSV from your business bank account)
• Sales invoices from your accounting software (Xero, QuickBooks, FreeAgent)
• Purchase invoices and expense records

The cleanest approach is to export a bank statement CSV that includes every transaction with: date, description, amount (positive for in, negative for out), and running balance. This single file can power the core of your dashboard.

The four measures every cash flow dashboard needs

1. Total cash in (period)

Sum of all positive transactions in the selected period. Filter to include only credits — payments received, transfers in, refunds received.

2. Total cash out (period)

Sum of all negative transactions in the selected period — supplier payments, payroll, rent, subscriptions, HMRC payments.

3. Net cash movement

Cash in minus cash out for the period. Positive means cash is building. Negative means cash is being consumed. This is the headline number on your dashboard.

4. Closing balance

The actual bank balance at the end of the selected period. Use the running balance from your bank statement rather than calculating it from transactions — it accounts for any transactions you may have missed.

Categorising your transactions

Raw bank transactions are useful but noisy. To make the dashboard actionable, categorise each transaction into a spending category: payroll, rent and rates, marketing, subscriptions, suppliers, HMRC, and so on.

The simplest way to do this in Power BI is a lookup table: a spreadsheet with two columns — a keyword that appears in transaction descriptions and the category to assign. Power Query can match each transaction description against the keyword list automatically.

This is not perfect — you will have an 'uncategorised' bucket — but it gets you 80% of the way there quickly and improves over time as you add more keywords.

What the dashboard should show

1. 1.Top row: cash in, cash out, net movement, closing balance — all for the current month
2. 2.Waterfall chart: showing opening balance, plus inflows, minus outflows, equals closing balance
3. 3.Monthly trend: 12-month bar chart of net cash movement — immediately shows cash-building vs cash-burning months
4. 4.Category breakdown: where the money is going, by category — a horizontal bar chart or table
5. 5.Rolling 30-day view: a line chart of the running balance day by day for the past 30 days

Setting up automatic refresh

For this to be useful rather than just a historical report, the data needs to update regularly. The cleanest approach: save your bank statement export to a SharePoint folder on a weekly or monthly basis, and set Power BI to refresh automatically from that folder.

Some banks and accounting platforms have Power BI connectors that refresh directly. Xero and QuickBooks both have connectors available — check the Power BI AppSource marketplace.