Forum Discussion
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 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.
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,
2 Replies
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.
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,
