cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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
Community Champion

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)

2 REPLIES 2
Community Champion

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)

Anonymous
Not applicable

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors