Subtract total from previous values

[sharifahy]

Hi, I've a table with packageid and expiry date. How to get a total of values where we can substract with the previous value? I need the values in new balance where it filter the max [update] then take the balance minus with [qty]. The next line of it will subtract the previous value according to the chronological [update] with their respective [packageid] and [expirydate].

Notice that in [packageid] for 0901, it has different expirydate so the new balance won't subtract the previous value. TQ!

packageid	expirydate	update	qty	balance	new balance
8875	03/08/2020	09/07/2020	90	100	100-90=10
8875	03/08/2020	09/06/2020	6	100	100-90-6=4
8875	03/08/2020	31/03/2020	1	100	100-90-6-1=3
8875	03/08/2020	21/01/2020	1	100	100-90-6-1-1=2
0901	31/12/2020	31/03/2020	1	1	1-1=0
0901	03/08/2020	31/03/2020	15	1	1-15=-14

[harshnathani · Accepted solution]

HI @sharifahy ,

You can try this Calculated Column

New Bal =
VAR b =
    CALCULATE (
        MAX ( 'Table'[update] ),
        FILTER (
            'Table',
            'Table'[packageid]
                = EARLIER ( 'Table'[packageid] )
        )
    )
VAR c = 'Table'[balance] - 'Table'[qty]
VAR d =
    CALCULATE (
        c,
        'Table'[update] = b
    )
VAR e =
    SUMX (
        FILTER (
            'Table',
            'Table'[packageid]
                = EARLIER ( 'Table'[packageid] )
                && 'Table'[update]
                    > EARLIER ( 'Table'[update] )
        ),
        'Table'[qty]
    )
RETURN
    IF (
        'Table'[update] = b,
        d,
        c - e
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[sharifahy]

Thank you so much! @harshnathani  It worked just how I want. Thanks!