How to Track Cash Flow in Power BI (Advanced)

Once you have a basic cash flow dashboard working in Power BI — bank transactions loaded, in/out measures built, a waterfall chart showing net movement — the next step is making it genuinely useful for financial management rather than just reporting.

This guide covers three improvements that move a cash flow dashboard from informational to decision-making.

1. Budget vs actuals

A cash flow dashboard that only shows actuals tells you what happened. Adding a budget comparison tells you whether what happened was expected — and by how much you were out.

To do this, you need a budget table: a simple spreadsheet with three columns — month, category, and budgeted amount. Load this into Power BI alongside your actuals, relate them on month and category, and build a variance measure: Variance = Actual - Budget.

Show variance as both an absolute number and a percentage. Flag categories where variance exceeds a threshold — say, 15% over or under budget — with conditional formatting. This makes the dashboard self-managing: it surfaces the things that need attention without anyone having to hunt for them.

2. Transaction categorisation at scale

Manual categorisation works for small transaction volumes. Once you have hundreds of transactions per month, you need a smarter approach.

The Power Query approach: create a lookup table with two columns — keyword and category. Use a merge query to match each transaction description against the keyword table. Any transaction that contains the keyword gets assigned the category. Transactions that match nothing go into 'Uncategorised' for manual review.

Over time, your keyword table grows and the uncategorised bucket shrinks. After a few months, 90%+ of transactions categorise automatically.

3. Automatic bank feed refresh

Manually exporting bank statements and saving them to SharePoint works but requires someone to remember to do it. A more reliable approach is a direct bank feed.

Several UK banks and open banking platforms expose transaction data via API. Tools like Plaid, TrueLayer, or Basiq can pull your bank transactions automatically into a format Power BI can read. This requires some technical setup but eliminates the manual export step entirely.

A simpler middle ground: set a calendar reminder to export and save the bank statement on the same day each week. The dashboard refreshes from SharePoint automatically — all you do is ensure the source file is current.