I’m new to PowerBi and still learning, and need some guidance. I have the following challenge
In the example data below. I have a table of invoices, mixed into that are what we call a credit rebills. One column contains service orders (column 4) that are generated and eventually get converted to invoices with unique numbers shown in (column 1). Periodically we have to credit the customer and create a new invoice (column 1) with a new invoice price. When a credit is created from the original SVO number a new transaction is created, it keeps the same SVO number but a new SVI or invoice is created with a new price. If I total up all the transactions in the table its not a true number until I pull out the transactions that fall under a credit rebill situation.
What I have to do is to identify any SVO that is a duplicate. Finding any that are duplicate means it was a credit rebill. Then I have to go back to column 1 and select the larger of the SVI numbers because it was the latest created. It will have the new price in Column 5. My thought was to create a calculated column that could capture all credit rebills. Any help on an approach would be greatly appreciated.
Hi @lomorris !
There might be are plenty of ways to solve this, but the fastest way that came to my mind is using DAX.
Firstly, I am not sure if you should rely only on dates to identify the most recent SVO since it is possible to get credits on the same date. So my solution is based on the invoice number. You could also use a combination of Invoice number and dates.
The solution only requires 2 steps
Identify the largest invoice number per each SVO, you could create a calculated column such as:
That’s all
I have attached a PBI file with the solution. I appreciate if you can mark the problem as solved. Any question don’t hesitate! Finance Solution Duplicated Values.pbix (91.2 KB)
Regards,
Since this is more of a data modelling issue, I would favor handling this in Power Query. Is using PowerQuery an option here? If so, please attach some dummy data and a quick mock up of what the ideal output would be.
Thanks for posting your question @lomorris. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
Thanks Diego, You are correct! I totally left out that in the case of an SVO the related SVI to use would the one with the highest number. I am ignoring dates. The larger number would the the latest invoice associated with the SVO.
Diego,
Thanks again, it works as expected. I will take some time now to review the Dax formula to get a more thorough understanding of how it works. Thanks again, exactly what I was looking for.
