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 ...
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.

     

     

     

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.