Forum Discussion

PPStar's avatar
PPStar
Icon for Helper V rankHelper V
3 years ago
Solved

Subtract from next row

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 .

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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

     

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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