Forum Discussion
Subtract total from previous values
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 |
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!
2 Replies
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!