Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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...
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.
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |