Forum Discussion

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

Use current Value to subtract new row value

Hello. 

i have the following table in Power BI 

I need a calculation that subtract Available QTY from QTY, but the answear must be used for the next calculation. 

 

e.g. 

 

47-14 = 23. Then i want to use 33 to subtract the next QTY which is 10, so 33-10 = 23

I have managed to get the Next QTY in a new column, as below

So the first line is easier, its 47-14 = 33.. 

Then i want to do 33 - 10 which is 23. 

Then 23 - 7 = 16 and so forth. 

 

Can someone please assist me in finding out how to do this. Via Power Query or DAX. 

 

Thanks

 

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

    Hi , PPStar 

    Thanks for your sample data first .

    Here are the steps you can refe to :
    (1)My test data is the same as yours.

    (2)We can create a calculated column in Power BI Desktop:

    Column = var _avaliable = [Avaliable QTY] 
var _index = [Index]
var _t = FILTER('Table','Table'[Index]<=_index)
return
_avaliable - SUMX(_t , [QTY])

     

    Then we can get the result as follows:

     

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

1 Reply

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

    Hi , PPStar 

    Thanks for your sample data first .

    Here are the steps you can refe to :
    (1)My test data is the same as yours.

    (2)We can create a calculated column in Power BI Desktop:

    Column = var _avaliable = [Avaliable QTY] 
var _index = [Index]
var _t = FILTER('Table','Table'[Index]<=_index)
return
_avaliable - SUMX(_t , [QTY])

     

    Then we can get the result as follows:

     

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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