Forum Discussion

mtrevisiol's avatar
mtrevisiol
Helper V
4 years ago
Solved

Difference between two tables

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!

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    4 years ago

    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.

     

     

     

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.