Forum Discussion
Dynamic Table based on Search
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 |
2 Replies
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
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
