Inventory Allocation to Work Order in DAX

[viren2023]

Hello everyone,

I am looking for a help to guide me with inventory availability check for work orders before making pick-list. I would like to see eligible work orders as per On Hand Qty.

I have attached a snapshot for reference.

I have been at it for a few days and I am unable to crack the logic.

Would be grateful for any suggestions on how to implement the logic.....

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file.  In another tab of the Excel file, show the expected result with your formulas/Pivot Table already written/built there.  I will try to translate the Excel formula logic into the DAX language.

[Anonymous]

Hi  @viren2023 ,

Not quite sure what you mean, is the expected result you want to arrive at all of the graphs above or a particular graph, what are the data used to arrive at the expected result, and what is the logic used to arrive at the result?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[viren2023]

Hi @Anonymous 

I would like to generate "Inventory Check" and "Quick View" structure based on "BOM", "Inventory", and "Work Orders" tables.

[viren2023]

BOM (Bill of Materials)		Inventory		Work Orders		Inventory Check		QuickView
Parent Part	Child Part	Qty Per		Item No	Qty On Hand		WorkOrder No	Parent Part	Work Order Qty Open		Work Order No	Parent Part	Child Part	Qty Per	Work Order Qty Open	Inventory Qry Required	Avail On Hand INV Qty after allocation	Work Order Qty Allocated	Status		WorkOrder No	Work Order Qty Open	Work Order Qty Allocated
1111	11	2		11	10		W001	1111	3		W001	1111	11	2	3	6	24	3	TRUE		W001	3	3
1111	12	1		11	10		W002	2222	4		W001	1111	12	1	3	3	12	3	TRUE		W002	4	4
1111	13	10		11	10		W003	1111	1		W001	1111	13	10	3	30	40	3	TRUE		W003	1	1
2222	21	5		12	10		W004	3333	5		W002	2222	21	5	4	20	10	4	TRUE		W004	5	5
2222	22	3		12	5		W005	3333	3		W002	2222	22	3	4	12	8	4	TRUE		W005	3	1
2222	11	4		13	50		W006	1111	2		W003	1111	11	2	1	2	22	1	TRUE		W006	2	0
2222	24	3		13	20						W003	1111	12	1	1	1	11	1	TRUE
3333	31	2		21	20						W003	1111	13	10	1	10	30	1	TRUE
3333	11	20		21	10						W004	3333	31	2	5	10	55	5	TRUE
4444	41	1		22	20						W004	3333	11	4	5	20	4	5	TRUE
4444	11	3		24	15						W005	3333	31	2	3	6	49	3	TRUE
4444	43	2		31	20						W005	3333	11	4	3	12	-8	1	FALSE
				31	10						W006	1111	11	2	2	4	-12	0	FALSE
				31	20						W006	1111	12	1	2	2	10	2	TRUE
				31	15						W006	1111	13	10	2	20	20	2	TRUE
				32	10
				41	5
				43	5