topic Re: Dynamic Table based on Search in DAX Commands and Tips

Dynamic Table based on Search

Resalat — Tue, 18 Jan 2022 09:14:40 GMT

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

Re: Dynamic Table based on Search

amitchandak — Tue, 18 Jan 2022 10:24:26 GMT

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

Re: Dynamic Table based on Search

Icey — Thu, 10 Feb 2022 08:34:43 GMT

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