I have a challenge that I’m not quite able to find content around despite my best effort at Google, YouTube and this forum.
I am looking to build a cash flow forecast based off of a dynamic list of unpaid invoices sent to customers. For each invoice I know the invoice number, customer name, payment due date and amount. I would like to be able to toggle the date in which we think we will be paid based on the due date - either sooner or later - to be reflected in a cash flow forecast.
The list of unpaid invoices will be changing over time as invoices are paid (dropping off) or sent (coming on). Is there a way to be able to toggle the date of each invoice via a parameter without having to manually create a parameter for each invoice that lands on the list?
I would add a column to your invoices called cluster number then
assign a cluster number to each invoice depending on the payment patterns you have identified. (per customer, per region,…) Or you can use your own algorithm.
Then you can create a clusters table, for each cluster, you assign a payment days’ offset.
Finally, you create a relationship, create a column called payment day forecast as [Due day]+RELATED(clusters[Offset]) and use the filter context to ignore the invoices already paid.
Not sure if this is what you are looking for,
Best
Diego
@diego Good thought, but that doesn’t quite get me to where I need to be. That would work for the initial forecasted payment date, but then I need to be able to toggle it from there.
For instance, if I am having a finance meeting with a client, I will say “we are expecting invoice 123 to be paid on 7/3/20” (based on something like the solution you outlined). Then, they will say “oh, we talked to that client the other day and they’re actually going to pay early on 6/30/20.” So, we would need some sort of a mechanism (like a parameter) to adjust the date -4, which will impact the whole cash flow forecast. At any given time, there could be 10 or so items we would have to adjust in the same way during the course of 1 conversation.
In this case, you could assign a “CurrentForecast” to each invoice.
The clusters table would be like, ClusterID | FcstDate | ForecastValue
You will need a formula that looks up for the most recent forecast in the clusters table.
You would have to update the forecast date and forecast value in each meeting.
The cashflow would be calculated automatically based on the current Fcst.
I imagine something like
HI @ChrisHervochon, did the response provided by @diego help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!