cancel
Showing results for
Did you mean:
Helper II

## 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
1 ACCEPTED SOLUTION
Super User III

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)

2 REPLIES 2
Super User III

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)

Helper II

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

Announcements