Subtract from next row

[PPStar]

Hello. 

I have the following (summarised table) in power bi

Date      |  QTY      | Order   | Measure 1

1/1/23  |  12          | 56          | 44

1/1/23  | 10           | 47          | 37

1/1/23  | 9             | 36          | 27

1/1/23   | 4             | 12          | 8

etc

I need to create a table that subtracts ORDERS from QTY , so 56 - 12 , 47 - 10 etc 

I have manged to do this, as show in Measure 1. 

The tricky part is, i want the Value of Measure 1 to now subtract the next QTY Value. 

So if Measure 1 is 44.... i want 44 - 10 (10 is the next value i need do a calculation agaist) = 34.... Then i want 34 - 9 = 35, then 35 - 4 so forth. 

Can anyone please assist me?. struggling. I beleive i somehow need to tell Power BI to go to the next line, but i dont know how to do that

Thanks in Advance .

[v-yanjiang-msft · Accepted solution]

Hi @PPStar ,

According to your description, here's my solution:

Measure =
MAXX ( ALL ( 'Table' ), 'Table'[Order] )
    - SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Order] >= MAX ( 'Table'[Order] ) ),
        'Table'[QTY]
    )

Get the correct result:

I attach my sample below for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang