Reply
D3K
Advocate II
Advocate II
Partially syndicated - Outbound

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

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

 

2022-07-21_20-10-59 (2).png

Any help will be highly appreciated! Thanks a lot!

4 REPLIES 4
lbendlin
Super User
Super User

Syndicated - Outbound

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

Syndicated - Outbound

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...

Syndicated - Outbound

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.

Syndicated - Outbound

@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!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)