Dynamic Table based on Search

[Resalat]

Dear all users

I am new to PowerBi and taking an online course. However, my management wants something, and I am unsure I can solve it. Furthermore, I am having trouble finding a way to solve it, either using DAX or Power Query. Here is a breakdown of my current situation with  Scrubbed tables.

We are a manufacturing company with a base ERP system; I have taken a dump of information from different modules. My task is to create a graph showing me the item's current stock at any given date.

Present Stock: This only shows the Present Stock current date. ( 1/18/2022 )

Item Code	Item Name	Current Stock
ELESTA300001	GI REDUCER SOCKET 3"X2" BSP	50
LBFGRS300200	GI TEE 2" BSP	45

Material Receive Now: This showed when the Factory received each item

Item Code	Item Name	MRN QTY	Date
ELESTA300001	GI REDUCER SOCKET 3"X2" BSP	100	1/10/2022
LBFGRS300200	GI TEE 2" BSP	100	1/10/2022

Material Issue: This shows that the store department issued this item to the end user reduces the stock effect.

Item Code	Item Name	Issue QTY	Date
ELESTA300001	GI REDUCER SOCKET 3"X2" BSP	60	1/15/2022
LBFGRS300200	GI TEE 2" BSP	50	1/5/2022

Based on the above information, it should create an auto table based on the item been searched; here is an example of the result I am trying to achieve.

Item	ELESTA300001
Date	MRN	Issue	Present Stock
01-Jan-22			10
02-Jan-22			10
03-Jan-22			10
04-Jan-22			10
05-Jan-22			10
06-Jan-22			10
07-Jan-22			10
08-Jan-22			10
09-Jan-22			10
10-Jan-22	100		110
11-Jan-22			110
12-Jan-22			110
13-Jan-22			110
14-Jan-22			110
15-Jan-22		60	50
16-Jan-22			50
17-Jan-22			50
18-Jan-22			50

[Icey]

Hi @Resalat ,

---

Item	ELESTA300001
Date	MRN	Issue	Present Stock
01-Jan-22			10
02-Jan-22			10
03-Jan-22			10
04-Jan-22			10
05-Jan-22			10
06-Jan-22			10
07-Jan-22			10
08-Jan-22			10
09-Jan-22			10
10-Jan-22	100		110
11-Jan-22			110
12-Jan-22			110
13-Jan-22			110
14-Jan-22			110
15-Jan-22		60	50
16-Jan-22			50
17-Jan-22			50
18-Jan-22			50

---

Is this the dynamic table you want to create? 

---

Item	ELESTA300001
Date	MRN	Issue	Present Stock
01-Jan-22			10

---

Where did this part come from? What format is the original table in?

Best Regards,

Icey

[amitchandak]

@Resalat , create a date and Item table  and join the two table with date and all three with item

Qunatity on hand = Sum(Present_stock[Current Stock]) -  CALCULATE(Sum(Material_receive[MRN ]) ,filter(allselected(Date),Date[Date] <=max(Date[Date]))) +CALCULATE(Sum(Material_Issue[MRN ]) ,filter(allselected(Date),Date[Date] <=max(Date[Date])))

In this I have assume you want to generate past stock