Forum Discussion

hmeegada's avatar
hmeegada
Frequent Visitor
7 years ago
Solved

eliminating reverse invoice data

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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 () )

    eliminating-reverse-invoice-data

     

     

    Best Regards,

bogomda's avatar
bogomda
Resolver I
7 years ago

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.