cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sharifahy
Helper II
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!

 

packageidexpirydateupdateqtybalancenew balance
887503/08/202009/07/202090100100-90=10
887503/08/202009/06/20206100100-90-6=4
887503/08/202031/03/20201100100-90-6-1=3
887503/08/202021/01/20201100100-90-6-1-1=2
090131/12/202031/03/2020111-1=0
090103/08/202031/03/20201511-15=-14
1 ACCEPTED SOLUTION
harshnathani
Super User III
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
    )

 

1.jpg

 


Regards,

Harsh Nathani


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

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

View solution in original post

2 REPLIES 2
harshnathani
Super User III
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
    )

 

1.jpg

 


Regards,

Harsh Nathani


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

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

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors