Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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,
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,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |