Calculate a positive number and a negative number according to a special rule

[NilsPR]

Hello 

I have a table with 4 important columns :

• Movement : 1 or 2 
• Quantity 
• ID of purchase for each line 
• Date

The movement 1 corresponds to a orderquantity and the movement 2 corresponds to a cancellation of 1 of the movement 1 thanks to the ID of purchase. So for example on the screenshot we can see a deliver an order quantity the 14 of February of 15.360 and the 19 a cancelation of the same quantity. You can see the ID of purchase is the same. So, how can I calculate to have 0 for this ID for example.

[v-yilong-msft · Accepted solution]

Hi @NilsPR ,

I create a new table as you mentioned.

Then I create a calculated column and here is the DAX code.

Column =
SUMX (
    FILTER (
        'Table',
        'Table'[ID of purchase for each line]
            = EARLIER ( 'Table'[ID of purchase for each line] )
    ),
    'Table'[Quantity]
)

Finally you will get the result you want.

Best Regards

Yilong Zhou

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

[NilsPR]

Hi @v-yilong-msft 

Thanks a lot for your help, it works well.

Best regards

Nils