Cash Forecast in Power Bi

Question

Hello - I am looking to create a cash forecast in PBI based off of certain criteria and wondering if anyone has ideas on the best way to go about this. AP only at the moment I am looking to create a table or calendar viewpoint based on due date and SAP payment runs as well as the bank balances as payments come out. Here is the sample data I am working with. Any thoughts?&nbsp;Company CodeInvoice NumberAmount DueDocument Due DateBank Account Paid FromBank BalanceKHLT54123&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.009/12/202351241345&nbsp;$ 100,000.00KHLT52145&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,000.009/20/202397898981&nbsp;$ 500,000.00KHLT58468&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.0010/1/202398734159&nbsp;$ 845,000.00KHLT58421&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,000.0010/16/202374582158&nbsp;$ 125,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Rules for KHLT&nbsp;&nbsp;SAP automated payment run daysMondayWednesdayFriday&nbsp;&nbsp;SAP picks up documents due up to and includingMonday + 7 business daysWednesday + 7 business daysFriday + 7 business days&nbsp;&nbsp;Bank clearing dayMonday + 2 business daysWednesday + 2 business daysFriday + 2 business days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company CodeInvoice NumberAmount DueDocument Due DateBank Account Paid FromBank BalanceLBUK74571&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 350.009/12/202351241345&nbsp;$ 100,000.00LBUK9514&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 950.009/20/202397898981&nbsp;$ 500,000.00LBUK8531&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,654.0010/1/202398734159&nbsp;$ 845,000.00LBUK85126&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,850.0010/16/202374582158&nbsp;$ 125,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Rules for LBUK&nbsp;&nbsp;SAP automated payment run daysMondayWednesday&nbsp;&nbsp;&nbsp;SAP picks up documents due up to and includingMonday + 7 working daysWednesday + 7 working days&nbsp;&nbsp;&nbsp;Bank clearing dayMonday + 2 working daysWednesday + 2 working days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

christinepayton · Answer

There's a couple of routes to go - you can either create a measure that approximates what you expect the forecast to be (using prior year's data for example) or you can try using the forecasting feature that's in the analytics tab of the line chart visual.&nbsp;

Forum Discussion

Cash Forecast in Power Bi

1 Reply

Related content

Forecasting with DAX

Forecasting

Cash Flow Forecast in Power BI

Power BI integrated forecast method

Forecast in Line Chart

Company Code	Invoice Number	Amount Due	Document Due Date	Bank Account Paid From	Bank Balance
KHLT	54123	$ 200.00	9/12/2023	51241345	$ 100,000.00
KHLT	52145	$ 25,000.00	9/20/2023	97898981	$ 500,000.00
KHLT	58468	$ 10,000.00	10/1/2023	98734159	$ 845,000.00
KHLT	58421	$ 5,000.00	10/16/2023	74582158	$ 125,000.00

Rules for KHLT
SAP automated payment run days	Monday	Wednesday	Friday
SAP picks up documents due up to and including	Monday + 7 business days	Wednesday + 7 business days	Friday + 7 business days
Bank clearing day	Monday + 2 business days	Wednesday + 2 business days	Friday + 2 business days

Company Code	Invoice Number	Amount Due	Document Due Date	Bank Account Paid From	Bank Balance
LBUK	74571	$ 350.00	9/12/2023	51241345	$ 100,000.00
LBUK	9514	$ 950.00	9/20/2023	97898981	$ 500,000.00
LBUK	8531	$ 3,654.00	10/1/2023	98734159	$ 845,000.00
LBUK	85126	$ 7,850.00	10/16/2023	74582158	$ 125,000.00


Rules for LBUK
SAP automated payment run days	Monday	Wednesday
SAP picks up documents due up to and including	Monday + 7 working days	Wednesday + 7 working days
Bank clearing day	Monday + 2 working days	Wednesday + 2 working days