A row separation for 2 by a condition in another column. Power Query

[D3K]

Hey everyone!

Fighting with next task and have no idea, how to solve it. Really need a help
We've got a list of invoices. And a planned sum of amount per month. 

Invoices are sorted by customer+year+period and Ascending sum of amount per invoice.

1. So the target is to find last invoice on which running total of Fact Amount becomes higher than Planned Amount.

2. Separate this invoice for two (rest of amount to achieve the sum of Total Planned Amount; the rest sum of this invoice)
3. Give the statuses for any of these rows depending on it's sense

4. All other invoices over the plan is resting with their values, but are changing their status for Over Plan

Please, see the image below

 

Any help will be highly appreciated! Thanks a lot!

[lbendlin]

This is likely too complex for Power BI.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[D3K]

Hey @lbendlin 
Thank you for your try to help.
Here is the link for Excel file with Existing and Target tables.
Also there is a formula, based on which I can make these calculation in Excel and it shows the whole logic
Thank you
https://www.dropbox.com/scl/fi/hdtrsq16p3s4jk7vf3bde/Sample-Power-BI.xlsx?dl=0&rlkey=t7pzrjzorevf802...

[lbendlin]

The first part (cumulative column N) can probably be done in Power BI.  But the splitting of the overshot invoice into two parts and the subsequent logic change is likely too much.  I would recommend you continue to use Excel.

Also note that your key column sort is alphabetic, not by date.

[D3K]

@lbendlin 
Sorry for disappered for a week, got private stuff.
Thank you for reviewed the case anyway.
Will try to make it with macro
Thanks!