Cash Flow Dashboard Power BI Tutorial: Build It Step by Step

A good cash flow dashboard in Power BI answers three questions at a glance: how much cash came in, how much went out, and how much will be left over the next few weeks. In this guide I will walk you through the exact build I use for small business clients, from a raw bank or ledger export to a model with inflow and outflow measures, a running balance and a 13-week cash view. By the end you will have a cash flow dashboard Power BI file that refreshes in one click when you drop in a new export.

Prepare the bank or ledger export in Power Query

Every cash flow model starts with one clean transactions table. Most bank exports give you a date, a description and either one signed amount column or separate debit and credit columns.

1. In Power BI Desktop, choose Home, Get data, Text/CSV (or Folder if you keep one file per month) and load the export.
2. Click Transform Data to open Power Query. Remove any summary rows at the top or bottom of the file and promote the real header row.
3. Set the data types: Date as Date, amounts as Decimal Number, text columns as Text. If your dates are in day/month/year format, right click the column and use Change Type, Using Locale so they are read correctly.
4. If you have separate Debit and Credit columns, add one signed Amount column (money in positive, money out negative) with Add Column, Custom Column.
5. Add a Category column. A simple way is a small mapping table (Description keyword, Category) merged into the transactions, or a Conditional Column for your top ten payees.
6. Rename the query to Transactions and click Close & Apply.

Here is the custom column formula for step 4. It treats blank cells as zero so you never get a null amount:

// Power Query custom column: signed Amount
(if [Credit] = null then 0 else [Credit])
  - (if [Debit] = null then 0 else [Debit])

Your final Transactions table should look like this: Date, Account, Description, Category, Amount. Keep it narrow. Every extra column makes the model bigger without making the dashboard better.

Add a Date table and an opening balance

Time based measures need a proper Date table. Go to Modeling, New table and paste the code below, then mark it as a date table (Table tools, Mark as date table) and create a relationship from Date[Date] to Transactions[Date].

Date =
VAR MinDate = DATE ( YEAR ( MIN ( Transactions[Date] ) ), 1, 1 )
VAR MaxDate = DATE ( YEAR ( TODAY () ) + 1, 12, 31 )   -- room for the forecast
RETURN
    ADDCOLUMNS (
        CALENDAR ( MinDate, MaxDate ),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "mmm yyyy" ),
        "MonthNo", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
        "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 1   -- Monday
    )

Sort the Month column by MonthNo so months appear in calendar order. Then create a one row table called Settings (use Enter data) with a column named Opening Balance that holds the bank balance on the day before your first transaction. This one number is what turns a list of movements into a real balance.

If you want to check the date table setup in more detail, Microsoft documents it in Set and use date tables in Power BI Desktop.

Write the inflow, outflow and running balance measures

With a signed Amount column, the core measures are short. Create them in a dedicated measures table so they are easy to find. I always show outflow as a positive number because people read “Outflow 42,000” more easily than “Outflow -42,000”.

Inflow = CALCULATE ( SUM ( Transactions[Amount] ), Transactions[Amount] > 0 )

Outflow = - CALCULATE ( SUM ( Transactions[Amount] ), Transactions[Amount] < 0 )

Net Cash Flow = [Inflow] - [Outflow]

Opening Balance = SUM ( Settings[Opening Balance] )

-- Balance at the end of whatever period is on the visual
Closing Balance =
VAR LastDateInContext = MAX ( 'Date'[Date] )
RETURN
    [Opening Balance]
        + CALCULATE (
            SUM ( Transactions[Amount] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Date] <= LastDateInContext )
        )

The Closing Balance measure is the running balance. For each month (or week, or day) on the axis it adds every transaction up to the last date of that period to the opening balance. Because it uses ALL on the Date table, it ignores the period filter for the sum but still respects filters on Account or Category if you add slicers.

A quick sanity check: put Closing Balance in a card, filter to the last statement date, and compare it with the closing balance on the bank statement. If they match, your model is right. If they do not, the gap is almost always a missing file, a duplicated month or a wrong opening balance.

Add a 13-week cash flow view

Monthly history is useful, but owners and finance managers usually want to see the next 13 weeks. That is roughly one quarter, short enough to forecast with confidence and long enough to spot a payroll week that will be tight.

For this you need a second table, Forecast, with Date, Category and Amount for expected receipts (positive) and payments (negative). It can be a simple Excel sheet you update every Monday: customer invoices due, rent, payroll, loan repayments, tax. Load it, relate Forecast[Date] to Date[Date], and add these measures:

Forecast Net = SUM ( Forecast[Amount] )

-- Actuals for past weeks, forecast from the current week onward
Projected Closing Balance =
VAR LastDateInContext = MAX ( 'Date'[Date] )
VAR CurrentWeekStart = TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
VAR ActualToDate =
    CALCULATE (
        SUM ( Transactions[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] <= MIN ( LastDateInContext, CurrentWeekStart - 1 )
        )
    )
VAR ForecastToDate =
    CALCULATE (
        SUM ( Forecast[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] >= CurrentWeekStart
                && 'Date'[Date] <= LastDateInContext
        )
    )
RETURN
    [Opening Balance] + ActualToDate + ForecastToDate

Now add a calculated column to the Date table that numbers weeks relative to today, so you can filter the view to the next 13 weeks without editing dates by hand:

WeekOffset =
VAR CurrentWeekStart = TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
RETURN DATEDIFF ( CurrentWeekStart, 'Date'[WeekStart], DAY ) / 7

Put WeekStart on the rows of a matrix, add Forecast Net and Projected Closing Balance as values, and set a visual level filter of WeekOffset between 0 and 12. Calculated columns are evaluated at refresh time, so the 13-week window moves forward every time you refresh the dataset.

Design the cash flow dashboard visuals

Keep the page simple. A cash dashboard is read in under a minute, usually by someone deciding whether to pay a supplier today or next week. This is the layout I use most often:

A few design habits make a big difference. Use one color for inflow and one for outflow across every visual. Add conditional formatting to the Projected Closing Balance column so any week below your minimum cash buffer turns red. And give each visual a plain title, such as “Where the money went this quarter”, instead of the default field names.

If you would like to see how this looks when finished, my accounting and cash flow Power BI dashboard project uses the same structure with a few extra pages for receivables and payables.

Common mistakes to avoid

• No opening balance. Without it, your running balance starts at zero and the line chart is meaningless.
• Mixed signs. Some exports show withdrawals as positive numbers. Fix the sign in Power Query, not in DAX, so every measure can trust the Amount column.
• Counting transfers as cash flow. A transfer between your own accounts is not income or spending. Give it a Transfer category and exclude it from Inflow and Outflow visuals.
• Using the date column from Transactions on visuals. Always use fields from the Date table, or weeks and months with no transactions will disappear.
• Categorizing by hand every month. Build a keyword mapping table once. I covered a fully automated version in my bank categorization project.

Frequently asked questions

Can I build this with the free version of Power BI?

Yes. Power BI Desktop is free and everything in this guide works there. You only need a Pro or Premium Per User license to publish and share the report with other people through the Power BI service.

How do I handle more than one bank account?

Append all exports into one Transactions table with an Account column, and store one opening balance per account in the Settings table with a relationship on Account. The same measures then work for each account and for the total.

Why does my 13-week view not move forward?

The WeekOffset column is calculated when the data refreshes, not when you open the report. Refresh the dataset, or schedule a daily refresh in the Power BI service, and the window will roll forward.

Want this built for your business?

If you would rather have a finished cash flow dashboard connected to your own bank or accounting exports, I build these regularly as part of my Power BI dashboard service. You can get an instant price estimate in a couple of minutes and decide from there.