eliminating reverse invoice data

[hmeegada]

Hi,

I have a database which records invoice level details. 

Initial invoice has a prefix of suffix of "A", If for some reason customer has entered wrong details, he then rasies a reverse invoice with a suffix of "B" which has an negative invoice amount of "A", which in effect will nullify the initial invoice.

Then the customer rasies another invoice with a suffix of "C".

I do not want to consider all invoices, I want to filter out ,

1. A invoices if there is no B invoice

2. C invocies whenever there is A and B.

Initial list

SL	Invoice
1	123456A
2	234567A
3	123456B
4	123123A
5	123456C

Expected result 

1	123456C
2	234567A
4	123123A

Thanks in advance.

[v-jiascu-msft · Accepted solution]

Hi @hmeegada ,

There are two solutions based on DAX. Please download the demo from the attachment. 

Solution 2 which doesn't need an additional column is like below.

Measure 2 =
VAR currentInvoiceNum =
    LEFT ( MIN ( Table1[Invoice] ), LEN ( MIN ( Table1[Invoice] ) ) - 1 )
VAR maxInvoice =
    CALCULATE (
        MAX ( Table1[Invoice] ),
        FILTER (
            ALL ( Table1 ),
            LEFT ( Table1[Invoice], LEN ( MIN ( Table1[Invoice] ) ) - 1 ) = currentInvoiceNum
        )
    )
RETURN
    IF ( MIN ( Table1[Invoice] ) = maxInvoice, 1, BLANK () )

Best Regards,

[bogomda]

see potential example to solution using M or DAX:

https://1drv.ms/x/s!AhUWZ84uo7UAglyw0wBKzzfdtToc

if data comes from ERP system, typically they all have reversal flag for credit invoices. see if you have it, so it might simplify things.