wants to write fourmula for usage details to mailtain stock

[umeshpatil1]

B	C	D	E		F	G	H	I	J
Date	Material	Qty	Used		Date	Material	Qty	Used	IN Stock
5/18/2021	Tube	10	5		5/13/2021	Tube	10	10	0
5/15/2021	fan	10	1		5/15/2021	fan	10	8	2
5/16/2021	LED	10	2		5/16/2021	LED	10	4	6
5/18/2021	Tester	10	5		5/18/2021	Tubes	10
5/20/2021	Screw	10	4

In B column i have used max date formula hence it will check & bring the max date in B column from F & G column. If i have purchased tues on 5/13/2021 10 Qty which i have used, again on 5/18/2021 i putchased 10 tubes in that case i want to campair date material detail with B & C with F& G if match  then i want E data to be print in I column....

[umeshpatil1]

Hi Yingjie Li,

Thanks for the replay,

I want to keep inventory stock material track. In above data column B,C,D and E i am making entry of purchased details. Column F,G and H is other (2nd work sheet) sheet here i am taking max (last material purchased details ) date data from 1st sheet in F column, material detail in G column and Qty H column. and in 3rd sheet i am entrying daily usage details when i put new entry it will check material detail and it will add that Qty in 2nd sheet I column.

If all tubes which i have purchased on 18th may 2021 (row 3) are used then when i when i purchase new tubes of 20 Qty then i will make entry in 1st sheet in that case in 2nd sheet its taking max date purchased tube details (applied max farmula) now i want to match 2nd sheet date,material name and take the used Qty from 2nd sheet & put it in 1st sheet used column E.

[v-yingjl]

Hi @umeshpatil1 ,

I'm still confusing about the I column result in table 2, how did you want to get that, could you please consider combine a specific row like fan, LED for a further explanation so that members in the communtiy could help you better.

Best Regards,
Community Support Team _ Yingjie Li

[v-yingjl]

Hi @umeshpatil1 ,

Based on your description, not certain what is your expected output. Have all of these columns from the same table and have the same column name?

Could you please consider sharing more details about it for further discussion? Sample data and expected output would help tremendously.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[umeshpatil1]

Date Material Qty Old Stock Used In Stock
13-May-2021 Water Solar Heater Vacuum Tube 10 0 10 10 0
13-May-2021 58MM Silicon Washers. 10 0 10 0 10
15-May-2021 Water Solar Heater Vacuum Tube 10 (Take F2 value) (C4+D4) 2 8
18-May-2021 Water Solar Heater Vacuum Tube 20 (Take F4 value last date stock value) (C5+D5) 0 28

[umeshpatil1]

Date(column A) Material (column B) Qty (column C) Old Stock (column D) Used (column E) In Stock (column F)

13-May-2021 Water Solar Heater Vacuum Tube 10 0 10 10 0
13-May-2021 58MM Silicon Washers. 10 0 10 0 10
15-May-2021 Water Solar Heater Vacuum Tube 10 (Take F2 value) (C4+D4) 2 8
18-May-2021 Water Solar Heater Vacuum Tube 20 (Take F4 value last date stock value) (C5+D5) 0 28