calculate new stock value

[shuijgen]

I have a table with stock values (lm_hoev) which is current value of a certain stock material item. 

production orders requires some amount (vbm1).

I would like to calculate the new stock value for that particular material item. 

So for instance for the first line there is 79 stock value and production requires 10 pieces. The new stock is then 69.

But for the second line the stock value is nog 79 but 69 and for the 3rd line the stock value is 59.

i have tried many things. Any help is appreciated.

[amitchandak]

@shuijgen ,

new column = [us_rsr_hoev] - sumx(filter(Table, [us_rsr_matnr] = earlier([us_rsr_matnr]) && [Index] <= earlier([Index]) ),[us_rsr_vbm1] )

[shuijgen]

I have made the new calculated column. It took 15 minutes (there are 1.488.575 rows in this table). Column does not result in correct answers. I expect :

1st row: lm_hoev (90) - vbm1 (10) = column (80)  ==> your calc gives -2790

2nd row: column (should have value of 80) - vbm1 (10) = column (70) ==> your calc gives -3010

3rd row: lm_hoev (should have value of 70) - vbm1 (10) = column (60) ==> your calc gives -3060

4th row: lm_hoev (should have value of 60) - vbm1 (10) = column (50) ==> your calc gives -3191

[shuijgen]

extra:

I filter the table every week (so for example this week I filter March 28 - April 4.

ut_rsr_capdate is related with Date table, where Date is the dates mentioned.

'public ut_resrmat_capt_Picken'[ut_rsr_capdate] >= MIN(D_DATE[Date]) -7,

'public ut_resrmat_capt_Picken'[ut_rsr_capdate] < MIN(D_DATE[Date]),