Running subtract in group by using first row value in other column

[BryanJun]

I have a table that contain item, qty_on_hand, and request_material.

There is a problem that my qty_on_hand is repeating and I only need one of these row to deduct the request material.

Is there any method can calculate the running balance in the same item group by using qty_on_hand deduct request material? (running balance can be negative since it can be shortage)

Table

Item	qty_on_hand	request_material
A	100	30
A	100	50
A	100	10
B	200	100
B	200	100
B	200	150

Above table shows qty_on_hand repeating, but i just need one of them to deduct the request_material (e.x: Item A only have 100 quantity on hand but it shows 3 times)

My expected output as below:

Table

Item	qty_on_hand	request_material	Runing Total
A	100	30	70
A	100	50	20
A	100	10	10
B	200	100	100
B	200	100	0
B	200	150	-150

[Ashish_Mathur]

Hi,

Do you have a Date column?  If yes, then it becomes simple to solve.  Also, it looks like you want a calculated column formula solution (and not a measure).  Am i correct?

[BryanJun]

yes, this table has contain a date column and it is better to deduct the earlier date first as well.

I'm prefer to create a new column in this table.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[BryanJun]

Hi, here's the PBI file.

you can access by this link : item.pbix

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

Cumulative material request = CALCULATE(SUM('Item'[material request]),FILTER('Item','Item'[Item]=EARLIER('Item'[Item])&&'Item'[Due date]<=EARLIER('Item'[Due date])))

Bal = 'Item'[stock]-'Item'[Cumulative material request]

The Cumulative material request entries in few rows are the same because the Due Date is the same.  In the Query Editor Group the Stock and material request by Item and Due Date.  For Stock, select the function as Min and for material, as SUM.

Hope this helps.

  

[BryanJun]

Hi,

Thanks a lot, this solution is near to my actual needed. 

Based your solution, i create an index column based on the due date.

Then make some changes on your code as below:

Cumulative material request = CALCULATE(SUM('Item'[material request]),FILTER('Item','Item'[Item]=EARLIER('Item'[Item])&&'Item'[Index]<=EARLIER('Item'[Index])))

Bal = 'Item'[stock]-'Item'[Cumulative material request]

This make the running result appear in the 'bal' column. 

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.