topic Re: Subtract total from previous values in DAX Commands and Tips

Subtract total from previous values

Anonymous — Thu, 09 Jul 2020 07:53:44 GMT

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

Re: Subtract total from previous values

Anonymous — Thu, 09 Jul 2020 09:19:27 GMT

HI Anonymous ,

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!

Re: Subtract total from previous values

Anonymous — Fri, 10 Jul 2020 03:10:22 GMT

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