Compare two tables and return the lines that doesnt match but allow for a tolerance of 1 dollar

[MegaOctane1]

i have two tables with Inovicenumber, date and amount.
I compare the two tables, and get a result. But because of minor differences in amounts, the invoices still show up in the list. how can i get this to return only those with amount difference <>1 usd?

My DAX function:

InvoiceDifferences = 
    VAR __Table1 = SELECTCOLUMNS(Table1, "InvoiceNumber", [InvoiceNumber], "Amount", [Amount], "Date", [Date])
    VAR __Table2 = SELECTCOLUMNS(Table2,  "InvoiceNumber", [InvoiceNumber], "Amount", [Amount], "Date", [Date])
    VAR __Table1Except = EXCEPT(__Table1, __Table2)
    VAR __Table2Except = EXCEPT( __Table2, __Table1)

    VAR __Result = 
        FILTER(
                UNION(
                ADDCOLUMNS(  __Table1Except, "Source", "Table1"),
                ADDCOLUMNS( __Table2Except, "Source", "Table2")
            ),
        [Amount] <> 0 
        )
RETURN
__Result

[Anonymous · Accepted solution]

Hi @MegaOctane1 ,

Based on the information you have provided, I have created the following example to help you solve the problem and you can follow the steps below:
1. Append two tables and differentiate the data of the two tables, add an index column.

2. Add two columns.

Table2difference = 
VAR _moverows =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
    )
VAR _amount1 =
    CALCULATE (
        MAX ( 'Table1'[Amount] ),
        FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) - _moverows )
    )
VAR _difference =
    ABS ( 'Table1'[Amount] - _amount1 )
RETURN
    _difference

InvoiceDifferences = 
VAR _moverows =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
    )
VAR _amount1 =
    CALCULATE (
        MAX ( 'Table1'[Table2difference] ),
        FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) + _moverows )
    )
RETURN
    IF ( 'Table1'[Source] = "Table2", 'Table1'[Table2difference], _amount1 )

3. Filter results with Flag=1.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.