Difference between two tables

[mtrevisiol]

Hi everyone. I've got two tables:

the first is the Sales Table

which contains, for each product, the customer code, the date in which the sale took place, the quantity sold and the sale type (always invoice).

The second table is the Picking Table:

which represents the pickings from the warehouse, specifying items, customer, date, picking quantity and the inventory reason.

I would like to compare the two tables making the difference between them. Usually the picking from the warehouse occurs from 0 to 5 days before the movement of invoicement. So I could compare the tables by considering the same item, the same customer and a DocumentDate that is <= PostingDate + 6 days, to get the rows that are present in the first table, but not in the second. This is the ideal result:

Here is the pbix file: https://www.dropbox.com/s/lltioca2vpnn1t7/DifferenceTables.pbix?dl=0

Thanks in advance!

[MFelix · Accepted solution]

Hi @mtrevisiol ,

First of all you need to create two table to addtional table (customer and Items) this will give you the dimension tables to relate your table then add the following measures:

Sum of Picking =
IF (
    ISBLANK ( SUM ( Tab1[SaleQty] ) ),
    BLANK (),
    CALCULATE (
        SUM ( Tab2[PickingQty] ),
        FILTER (
            ALL ( Tab2[PostingDate] ),
            Tab2[PostingDate]
                >= MAX ( Tab1[DocumentDate] ) - 6
        )
    )
)


Difference = if( (SUM(Tab1[SaleQty]) - [Sum of Picking])  = 0 , BLANK(),  (SUM(Tab1[SaleQty]) - [Sum of Picking]) )

Result below and attach.

[mtrevisiol]

Thank you @MFelix !