Multiple line Qty Subtraction from Single Data

[mageshraja]

Hi ,

Hope this would be simple doubt. But I need help to crack the solution in Power bi

Have Two tables:

Table 1: Same part # stock received on muliple dates / Unique Ref. 

Table 2: Usage of the parts without any reference

Output Needed :

1. Each line in Table 1 should have a new coulum with the Qty used, which should not be greater than the received qty

2. The Qty considereded for the previous line from table should be deducted for the next line calculation

Table 1
Unique Ref ID	Part #	Received Qty
ID1	A	2000
ID2	A	2000
ID3	A	2000
ID4	A	2000
ID5	B	2500

Table 2
Part #	Qty Used
A	7000
B	5000

OutPut Expected
Unique Ref ID	Part #	Qty	Qty Used (Table 2)
ID1	A	2000	2000
ID2	A	2000	2000
ID3	A	2000	2000
ID4	A	2000	1000
ID5	B	2500	2500

Thanks

Mak

[amitchandak · Accepted solution]

Try like new columns

Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty])
col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used])
col3 = if((col2-col1)<col2,(col2-col1),col2)

Appreciate your Kudos.

[mageshraja]

Hi Amit,

It works. Thanks for the solution.

Regards,

Mak

[Greg_Deckler]

First, can you guarantee that Unique Ref ID will always sort alphabetically such that the earliest "instances" are always alphabetically "less" than later "instances". If that cannot be guaranteed, do you have a date column or index column where you can distinguish "earlier" from "later"?

Because if you don't, this will be difficult if not impossible.

[mageshraja]

Hi Greg,

Yes , the unique ref ID are sequenced numeric data which can be sorted from earliest. In that case what solution can support in this case.

Thanks,

Magesh