# Calculated Column or Dax or combination

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.

@lomorris Can you please share sample data and required output screen shot(How you want to display on report)

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

1. Identify the largest invoice number per each SVO, you could create a calculated column such as:

LargestInvoice = MAXX(FILTER(Orders,Orders[SVO]=EARLIER(Orders[SVO])),Orders[Invoice Number])

1. Filter the most recent invoices using in a new calculated table

SalesTable = FILTER(Orders,Orders[Invoice Number]=Orders[LargestInvoice])

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,

Diego

1 Like

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.

Happy to help and thanks for marking the solution, you can also solve it using M as the expert said

@lomorris

As @diego mentioned there are many approaches for your requirement .

Here is another simple solution
Just create Rank calculated column… That’s it

2 Likes