Tag: Account Receavables

Paste your bank export and invoice list, click Refresh, and see the week you run short. Cash Flow is a Power BI dashboard for small businesses that answers three questions: how much cash do we have, who owes us, and will we be okay? Paste your bank export and your invoice list into one Excel workbook, click Refresh, and you get your cash position, an aging report and a 13-week forecast. The forecast is the part that is hard to build yourself. It places every open invoice on the date that customer usually pays, adds your open bills and the payments you already know about, and shows the week your balance is lowest, months before it happens. The finished Cash Flow dashboard Prefer the ready-made template? Get Cash Flow for $19, with the dashboard, the Excel file and a step-by-step guide: Click to View Template Watch the full build on YouTube: https://www.youtube.com/@PivotalstatsProjects (paste the video link here) What you need Power BI Desktop from Microsoft (Windows), 2025 or newer Microsoft Excel The sample data file Cash Flow.xlsx: Click to Download What you'll build A cash flow dashboard for a small business, built in Power BI from one Excel workbook: your cash position, who owes you money and how late they are, and a 13-week forecast that shows the week your balance is lowest. It uses Power Query, a small star schema, DAX and only Microsoft's standard visuals, with an app-style design whose logo, icons and illustrations you can replace. $431K owed $80K in the bank Step 01: The three pages Overview answers how much cash you have: the balance, its change in three months, money in and out for the last 30 days, what customers owe and how much is overdue, plus the balance history with the forecast and the buffer line. Receivables answers who owes you: five aging cards, every customer with a coloured bar, the average days to get paid and a chase list. Forecast answers whether you will be okay: the lowest point in the next 13 weeks against your buffer, week by week. How much cash do we have? Who owes us? Will we be okay? Step 02: One Excel workbook, six tables Bank is your bank export: Date, Description, Category and Amount, with money in positive and money out negative. Invoices and Bills have a Paid Date column that stays empty until the money moves. Customers holds a segment and your payment terms. Planned holds the money you already know about: an amount, a frequency (Once, Weekly, Every 2 weeks or Monthly) and the next date. Setup has two values: the balance before the first bank row and your cash buffer. Every tab is an Excel table, so it grows as you paste rows underneath. Bank Paid Date Planned Step 03: Connect with one parameter In Power Query, create a text parameter called DataFile that holds the full path to the workbook. Every query reads from it, so moving the file means changing one value. Then add a blank query called Workbook that opens the file with Excel.Workbook. It lists the sheets and the tables; always read the tables, because they grow with your data. Switch off Enable load: it is only the shared source for the other queries. DataFile parameter Read the tables let Source = Excel.Workbook(File.Contents(DataFile), null, true) in Source Step 04: Clean the bank export BankRaw selects the four columns by name (a missing column arrives empty instead of breaking the refresh), trims the text and sets the types. Rows that fail as a date or a number, such as a totals line, are removed, and only rows with both a date and an amount are kept. Rows without a category get the label Uncategorised, so nothing silently drops out of the charts. Selected Columns Junk rows removed M let Source = Workbook{[Item = "Bank", Kind = "Table"]}[Data], #"Selected Columns" = Table.SelectColumns(Source, {"Date", "Description", "Category", "Amount"}, MissingField.UseNull), #"Trimmed Text" = Table.TransformColumns(#"Selected Columns", {{"Description", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Category", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"Date", type date}, {"Amount", type number}}, "en-US"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Date", "Amount"}), #"Kept Bank Rows" = Table.SelectRows(#"Removed Errors", each [Date] null and [Amount] null), #"Default Category" = Table.ReplaceValue(#"Kept Bank Rows", null, "Uncategorised", Replacer.ReplaceValue, {"Category"}) in #"Default Category" Step 05: Invoices and usual days late The Customers query works out how late each customer usually pays: it takes the invoices paid in the last 180 days, counts the days between due date and paid date, and averages them per customer. Customers that appear in Invoices but not in the Customers tab are added automatically. The Invoices query then adds Status (Open while Paid Date is empty), Days Overdue, an aging Bucket and Expected Date: the due date plus that customer's usual days late, or two weeks from the latest bank date if that has already passed. Usual days late 53 and 71 days Three helper columns M let Source = Workbook{[Item = "Customers", Kind = "Table"]}[Data], #"Selected Columns" = Table.SelectColumns(Source, {"Customer", "Segment", "Terms (days)"}, MissingField.UseNull), #"Renamed Columns" = Table.RenameColumns(#"Selected Columns", {{"Terms (days)", "Terms"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns", {{"Customer", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Segment", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"Terms", Int64.Type}}), #"Known Customers" = Table.Distinct(Table.SelectRows(#"Changed Type", each [Customer] null and [Customer] ""), {"Customer"}), #"New From Invoices" = List.Difference(List.Distinct(InvoicesRaw[Customer]), #"Known Customers"[Customer]), #"Missing Customers" = Table.FromRecords(List.Transform(#"New From Invoices", each [Customer = _, Segment = "Other", Terms = 30])), #"All Customers" = if List.IsEmpty(#"New From Invoices") then #"Known Customers" else Table.Combine({#"Known Customers", #"Missing Customers"}), #"Default Segment" = Table.ReplaceValue(#"All Customers", null, "Other", Replacer.ReplaceValue, {"Segment"}), #"As Of" = List.Max(BankRaw[Date]), #"Recently Paid" = Table.SelectRows(InvoicesRaw, each [PaidDate] null and [PaidDate] >= Date.AddDays(#"As Of", -180)), #"Added Days Late" = Table.AddColumn(#"Recently Paid", "Late", each List.Max({0, Duration.Days([PaidDate] - [DueDate])}), Int64.Type), #"Usual Days Late" = Table.Group(#"Added Days Late", {"Customer"}, {{"UsualDaysLate", each Number.Round(List.Average([Late])), Int64.Type}}), #"Merged Usual" = Table.NestedJoin(#"Default Segment", {"Customer"}, #"Usual Days Late", {"Customer"}, "U", JoinKind.LeftOuter), #"Expanded Usual" = Table.ExpandTableColumn(#"Merged Usual", "U", {"UsualDaysLate"}), #"Default Usual" = Table.ReplaceValue(#"Expanded Usual", null, 0, Replacer.ReplaceValue, {"UsualDaysLate"}), #"Typed" = Table.TransformColumnTypes(#"Default Usual", {{"Customer", type text}, {"Segment", type text}, {"Terms", Int64.Type}, {"UsualDaysLate", Int64.Type}}) in #"Typed" M let #"As Of" = List.Max(BankRaw[Date]), #"Merged Customers" = Table.NestedJoin(InvoicesRaw, {"Customer"}, CustomersAll, {"Customer"}, "C", JoinKind.LeftOuter), #"Expanded Usual" = Table.ExpandTableColumn(#"Merged Customers", "C", {"UsualDaysLate"}), #"Added Status" = Table.AddColumn(#"Expanded Usual", "Status", each if [PaidDate] = null then "Open" else "Paid", type text), #"Added Days Overdue" = Table.AddColumn(#"Added Status", "DaysOverdue", each if [PaidDate] = null then List.Max({0, Duration.Days(#"As Of" - [DueDate])}) else null, Int64.Type), #"Added Bucket" = Table.AddColumn(#"Added Days Overdue", "Bucket", each if [PaidDate] null then "Paid" else if [DaysOverdue] = 0 then "Not due yet" else if [DaysOverdue] <= 30 then "1-30 days" else if [DaysOverdue] <= 60 then "31-60 days" else if [DaysOverdue] <= 90 then "61-90 days" else "Over 90 days", type text), #"Added Days To Pay" = Table.AddColumn(#"Added Bucket", "DaysToPay", each if [PaidDate] = null then null else Duration.Days([PaidDate] - [InvoiceDate]), Int64.Type), #"Added Expected Date" = Table.AddColumn(#"Added Days To Pay", "ExpectedDate", each if [PaidDate] null then null else let Usual = Date.AddDays([DueDate], if [UsualDaysLate] = null then 0 else [UsualDaysLate]) in if Usual > #"As Of" then Usual else Date.AddDays(#"As Of", 14), type date), #"Removed Usual" = Table.RemoveColumns(#"Added Expected Date", {"UsualDaysLate"}) in #"Removed Usual" Step 06: Build the forecast table Forecast is one table of every amount expected in the next 13 weeks. Open invoices go in on their expected date as money in, open bills on their due date as money out. Each Planned row becomes several rows: a custom column builds a list of dates with List.Dates (weekly and two-weekly) or Date.AddMonths (monthly), keeps the dates inside the window, and the list is expanded so each date is its own row. Append the three parts and you have Date, Type, Name and Amount. Three sources A list of dates per row One tidy table M let #"As Of" = List.Max(BankRaw[Date]), #"Forecast End" = Date.AddDays(#"As Of", 91), #"Open Invoices" = Table.SelectRows(InvoicesAll, each [Status] = "Open"), #"Invoice Rows" = Table.FromRecords(Table.TransformRows(#"Open Invoices", each [Date = [ExpectedDate], Type = "Invoice", Name = [Customer] & " " & [InvoiceNo], Amount = [Amount]])), #"Open Bills" = Table.SelectRows(BillsAll, each [Status] = "Open"), #"Bill Rows" = Table.FromRecords(Table.TransformRows(#"Open Bills", each [Date = List.Max({[DueDate], Date.AddDays(#"As Of", 1)}), Type = "Bill", Name = [Supplier] & " " & [BillNo], Amount = - [Amount]])), #"Planned Source" = Workbook{[Item = "Planned", Kind = "Table"]}[Data], #"Planned Columns" = Table.SelectColumns(#"Planned Source", {"Item", "Amount", "Frequency", "Next Date"}, MissingField.UseNull), #"Planned Text" = Table.TransformColumns(#"Planned Columns", {{"Item", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Frequency", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Planned Typed" = Table.TransformColumnTypes(#"Planned Text", {{"Next Date", type date}, {"Amount", type number}}, "en-US"), #"Planned Kept" = Table.SelectRows(#"Planned Typed", each [Item] null and [Amount] null and [Next Date] null), #"Added Dates" = Table.AddColumn(#"Planned Kept", "Date", each let Step = if [Frequency] = "Weekly" then 7 else if [Frequency] = "Every 2 weeks" then 14 else 0, All = if [Frequency] = "Monthly" then List.Transform({0 .. 4}, (i) => Date.AddMonths([Next Date], i)) else if Step > 0 then List.Dates([Next Date], 14, #duration(Step, 0, 0, 0)) else {[Next Date]} in List.Select(All, (d) => d > #"As Of" and d <= #"Forecast End")), #"Expanded Dates" = Table.ExpandListColumn(#"Added Dates", "Date"), #"Planned Rows" = Table.FromRecords(Table.TransformRows(Table.SelectRows(#"Expanded Dates", each [Date] null), each [Date = [Date], Type = "Planned", Name = [Item], Amount = [Amount]])), #"Appended" = Table.Combine({#"Invoice Rows", #"Bill Rows", #"Planned Rows"}), #"In Window" = Table.SelectRows(#"Appended", each [Date] null and [Date] <= #"Forecast End"), #"Changed Type" = Table.TransformColumnTypes(#"In Window", {{"Date", type date}, {"Type", type text}, {"Name", type text}, {"Amount", type number}}) in #"Changed Type" Step 07: Model and date table The Date table is DAX: CALENDAR from 1 January of the first year to the end of the month 13 weeks after the last bank row. WeekStart gives each date its Monday, WeekOffset counts weeks from the latest bank date (0 is this week, 13 is the end of the forecast) and MonthOffset does the same for months. Mark it as a date table, add a small Aging table with the five buckets, and relate Bank, Invoices and Forecast to Date, and Invoices to Customers and Aging. Date table in DAX WeekStart and WeekOffset DAX Date = VAR _first = MIN ( MIN ( Bank[Date] ), MIN ( Invoices[InvoiceDate] ) ) VAR _asof = MAX ( Bank[Date] ) VAR _asofWeek = _asof - WEEKDAY ( _asof, 2 ) + 1 RETURN ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( _first ), 1, 1 ), EOMONTH ( _asof + 91, 0 ) ), "Year", YEAR ( [Date] ), "MonthNum", MONTH ( [Date] ), "Month", FORMAT ( [Date], "mmm", "en-US" ), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm", "en-US" ) & UNICHAR ( 10 ) & FORMAT ( [Date], "yy", "en-US" ), "MonthOffset", ( YEAR ( [Date] ) - YEAR ( _asof ) ) * 12 + MONTH ( [Date] ) - MONTH ( _asof ), "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 1, "Week", FORMAT ( [Date] - WEEKDAY ( [Date], 2 ) + 1, "d mmm", "en-US" ) & UNICHAR ( 10 ) & FORMAT ( [Date] - WEEKDAY ( [Date], 2 ) + 1, "yyyy", "en-US" ), "WeekSort", VALUE ( FORMAT ( [Date] - WEEKDAY ( [Date], 2 ) + 1, "yyyymmdd" ) ), "WeekOffset", INT ( ( [Date] - WEEKDAY ( [Date], 2 ) + 1 - _asofWeek ) / 7 ) ) DAX Aging = DATATABLE ( "Bucket", STRING, "BucketSort", INTEGER, { { "Not due yet", 1 }, { "1-30 days", 2 }, { "31-60 days", 3 }, { "61-90 days", 4 }, { "Over 90 days", 5 } } ) Step 08: Cash and receivables measures As Of is the latest bank date. Cash Balance is the opening balance plus every bank amount up to the date in context, never past As Of; Cash Today is the same with the date filter removed. Owed To Us sums the open invoices and Overdue adds the condition that days overdue is above zero. Days To Get Paid averages the days from invoice to payment by the month the money arrived. TREATAS applies the dates in context to the Paid Date column, so no second relationship is needed. Cash Balance Owed To Us and Overdue DAX As Of = CALCULATE ( MAX ( Bank[Date] ), REMOVEFILTERS () ) DAX Cash Balance = VAR _d = MIN ( MAX ( 'Date'[Date] ), [As Of] ) RETURN [Opening Balance] + CALCULATE ( SUM ( Bank[Amount] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] 0 ) ) DAX Days To Get Paid = CALCULATE ( AVERAGE ( Invoices[DaysToPay] ), REMOVEFILTERS ( 'Date' ), TREATAS ( VALUES ( 'Date'[Date] ), Invoices[PaidDate] ) ) Step 09: Projected balance and the low point Projected Balance is Cash Today plus every forecast amount up to the date in context. Lowest Balance evaluates it at the end of each of the 14 forecast weeks and returns the smallest with MINX; Lowest Week finds which week that was. Buffer Gap subtracts the cash buffer from the lowest balance. A negative number means you fall under your buffer, and you know the week. Projected Balance Lowest Balance DAX Projected Balance = VAR _d = MAX ( 'Date'[Date] ) RETURN [Cash Today] + CALCULATE ( SUM ( Forecast[Amount] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] = 0 && 'Date'[WeekOffset] = 0 && 'Date'[WeekOffset] Canvas background with Fit and 0% transparency. A theme file sets the fonts and colours and removes the background and border from every visual. Everything a user might change sits on top as a real object: the logo is an image visual, the brand name and page title are text boxes, and the menu items are buttons with a page navigation action and their own icon images. Step 11: KPI chips drawn by DAX Each chip is one DAX measure that returns an SVG picture as text: the value, the change against a year ago in a green or red pill, and a sparkline of twelve month-end values. Set the measure's data category to Image URL and show it in an image visual. The 3D icon on each chip is a separate image visual, so it can be replaced without touching the DAX. The large hero card works the same way: the illustration is in the background and the numbers are a measure on top. DAX Chip Owed = VAR _v = [Owed To Us] VAR _p = ( VAR _then = [As Of] - 365 RETURN CALCULATE ( [Owed At], REMOVEFILTERS ( 'Date' ), 'Date'[Date] = _then ) ) VAR _d = IF ( ISBLANK ( _p ) || _p = 0, BLANK (), DIVIDE ( _v - _p, ABS ( _p ) ) * 100 ) VAR _has = NOT ISBLANK ( _d ) VAR _fg = IF ( NOT _has, "rgb(95,117,134)", IF ( _d <= 0, "rgb(14,159,110)", "rgb(225,29,72)" ) ) VAR _bg = IF ( NOT _has, "rgb(238,243,245)", IF ( _d <= 0, "rgb(227,247,236)", "rgb(253,232,236)" ) ) VAR _dt = IF ( _has, FORMAT ( ABS ( _d ), "0.0", "en-US" ) & "", "n/a" ) VAR _pw = 32 + LEN ( _dt ) * 7.2 VAR _asof = [As Of] VAR _t = ADDCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "@v", VAR _e = MIN ( EOMONTH ( _asof, [Value] - 12 ), _asof ) VAR _s = DATE ( YEAR ( _e ), MONTH ( _e ), 1 ) RETURN CALCULATE ( [Owed At], REMOVEFILTERS ( 'Date' ), 'Date'[Date] = _e ) + 0 ) VAR _mn = MINX ( _t, [@v] ) VAR _mx = MAXX ( _t, [@v] ) VAR _pts = CONCATENATEX ( _t, FORMAT ( 22 + ( [Value] - 1 ) / 11 * 189, "0.0", "en-US" ) & "," & FORMAT ( 276 - DIVIDE ( [@v] - _mn, _mx - _mn, 0.5 ) * 26, "0.0", "en-US" ), " ", [Value], ASC ) VAR _spark = "" & "" VAR _txt = IF ( ISBLANK ( _v ), "-", IF ( ABS ( _v ) >= 1000000000, FORMAT ( _v / 1000000000, "$#,0.00", "en-US" ) & "B", IF ( ABS ( _v ) >= 1000000, FORMAT ( _v / 1000000, "$#,0.0", "en-US" ) & "M", IF ( ABS ( _v ) >= 10000, FORMAT ( _v / 1000, "$#,0.0", "en-US" ) & "K", FORMAT ( _v, "$#,0", "en-US" ) ) ) ) ) RETURN "data:image/svg+xml;utf8," & "Owed to you" & "" & _txt & "" & "" & IF ( NOT _has, "", IF ( _d >= 0, "", "" ) ) & "" & _dt & "" & "vs a year ago" & _spark & "" Step 12: Balance and weekly charts The cash balance chart is a line chart on WeekStart with three measures. Each returns a value only inside its own window: the actual balance for the last 26 weeks, the projected balance from today to week 13, and the buffer across both. Because they are blank elsewhere, the chart needs no filters and moves forward with new data. The weekly forecast is a line and clustered column chart: money in as a column going up, money out going down, and the projected balance and buffer as lines on the same axis. Three measures, three windows Combo chart DAX Wk Balance = VAR _o = MAX ( 'Date'[WeekOffset] ) RETURN IF ( HASONEVALUE ( 'Date'[WeekStart] ) && _o >= -26 && _o = 0 && _o = 0 && _o = 0 && _o <= 13, - [Forecast Out] + 0 ) Step 13: Aging cards and the chase list Each aging card is a measure that filters Owed To Us to one bucket, counts the invoices and draws the share as a bar. The customer table adds two small pictures per row: an avatar with the customer's initials and a bar that shows how much is owed and how late it is. The chase list shows every overdue invoice, oldest first, with the days late. One measure per card Halden and Orion DAX Bucket 1-30 days = VAR _v = CALCULATE ( [Owed To Us], Aging[Bucket] = "1-30 days" ) + 0 VAR _n = CALCULATE ( [Open Invoices], Aging[Bucket] = "1-30 days" ) + 0 VAR _share = DIVIDE ( _v, [Owed To Us], 0 ) RETURN "data:image/svg+xml;utf8," & "" & "" & "1-30 days" & "" & FORMAT ( _n, "0", "en-US" ) & IF ( _n = 1, " invoice", " invoices" ) & "" & "" & IF ( ABS ( _v ) >= 1000000000, FORMAT ( _v / 1000000000, "$#,0.00", "en-US" ) & "B", IF ( ABS ( _v ) >= 1000000, FORMAT ( _v / 1000000, "$#,0.0", "en-US" ) & "M", IF ( ABS ( _v ) >= 10000, FORMAT ( _v / 1000, "$#,0.0", "en-US" ) & "K", FORMAT ( _v, "$#,0", "en-US" ) ) ) ) & "" & "" & FORMAT ( _share * 100, "0", "en-US" ) & "" & "" & "" & "" DAX Avatar = VAR _n = SUBSTITUTE ( SELECTEDVALUE ( Customers[Customer] ), "& ", "" ) VAR _p = SEARCH ( " ", _n, 1, 0 ) VAR _ini = UPPER ( LEFT ( _n, 1 ) & IF ( _p > 0, MID ( _n, _p + 1, 1 ) ) ) VAR _k = MOD ( UNICODE ( LEFT ( _n, 1 ) ) + LEN ( _n ), 6 ) VAR _bg = SWITCH ( _k, 0, "rgb(231,248,241)", 1, "rgb(232,233,254)", 2, "rgb(254,243,215)", 3, "rgb(255,228,232)", 4, "rgb(221,243,254)", 5, "rgb(221,244,241)" ) VAR _fg = SWITCH ( _k, 0, "rgb(5,150,105)", 1, "rgb(79,70,229)", 2, "rgb(180,83,9)", 3, "rgb(190,18,60)", 4, "rgb(3,105,161)", 5, "rgb(15,118,110)" ) RETURN IF ( NOT ISBLANK ( _n ) && NOT ISBLANK ( [Owed To Us] ), "data:image/svg+xml;utf8," & "" & "" & _ini & "" ) DAX Late Pill = VAR _d = [Chase Days] VAR _c = SWITCH ( TRUE (), _d > 90, "rgb(225,29,72)", _d > 60, "rgb(251,113,133)", _d > 30, "rgb(251,146,60)", "rgb(245,158,11)" ) VAR _t = FORMAT ( _d, "0", "en-US" ) & IF ( _d = 1, " day late", " days late" ) RETURN IF ( NOT ISBLANK ( _d ), "data:image/svg+xml;utf8," & "" & "" & "" & _t & "" ) Step 14: Update it, then test a plan When a customer pays, add the bank rows, fill in Paid Date on those invoices, save and click Refresh. Cash goes up and overdue goes down, but the forecast's low point may not move, because it already expected that money. To test a fix, add a row to the Planned tab, for example project deposits in December, and refresh again to see whether the lowest point clears your buffer. Halden pays After the refresh Still $46.1K in December Wrap-up You've now built a complete cash flow dashboard: Power Query with a single file-path parameter and forgiving queries, a forecast table built from open invoices, bills and planned payments, a small star schema with a DAX date table, and an app-style design using only standard visuals. If you'd rather start from the finished file, the ready-made template includes the dashboard, the Excel workbook and a step-by-step setup guide. Prefer the ready-made template? Get Cash Flow for $19, with the dashboard, the Excel file and a step-by-step guide: Click to View Template More dashboards: https://www.youtube.com/@PivotalstatsProjects · https://pivotalstats.com

Tag: Account Receavables

Build a Cash Flow Dashboard in Power BI (13-Week Forecast and Who Owes You, from One Excel File)

Quick Links

Resources

Policies