Forum Discussion
Inventory Forecast Isuue
Hello,
I am currently working on an inventory forecasting project and need some assistance with creating the appropriate DAX formulas in Power BI. Here is the structure of my data:
1. Inventory: This table contains the initial quantity of each product, identified by a Product Code.
2. Master Item: This table includes details for each product, identified by a Product Code.
3. PO (Purchase Orders): This table records inventory additions, identified by Product Code and Date.
4. SC (Supplier Consignments): This table also records inventory additions, identified by Product Code and Date.
5. SO (Sales Orders): This table records inventory deductions, identified by Product Code and Date.
6. BOM (Bill of Materials): This table records inventory deductions, identified by Product Code and Date.
7. Calendar: This table includes Date information for time-based analysis.
I have established relationships between these tables based on Product Code and Date. What I need help with is creating the DAX formulas to calculate the following:
| Product Code | January |
| Feb | ||||||||
| Inventory | PO(+) | SC(+) | SO(-) | BOM(-) | Balance | PO(+) | SC(+) | SO(-) | BOM(-) | Balance | |
| AB101 | 10 | 10 | 10 | 5 | 5 | 20 | 5 | 20 | 20 | 10 | 15 |
| AB102 | 20 | 5 | 10 | 10 | 5 | 20 | 5 | 10 | 10 | 15 | 10 |
9 Replies
Hi Anonymous ,
The Table data is shown below:
Please follow these steps:
1.Add index column after grouping
Table.AddIndexColumn([Column],"Index",1)2.Use the following DAX expression to create a measure
Balance = VAR _Product_Code = SELECTEDVALUE ( 'Table'[Product Code] ) VAR _Month = SELECTEDVALUE ( 'Table'[Month] ) VAR _table = SUMMARIZE ( ALL ( 'Table' ), [Product Code], [Month], "Index", MAX ( 'Table'[Index] ), "Column", SUM ( 'Table'[Inventory] ) + SUM ( 'Table'[PO] ) + SUM ( 'Table'[SC] ) - SUM ( 'Table'[SO] ) - SUM ( 'Table'[BOM] ) ) VAR _table2 = ADDCOLUMNS ( _table, "Column2", MAXX ( FILTER ( _table, [Product Code] = EARLIER ( [Product Code] ) && [Index] = EARLIER ( [Index] ) - 1 ), [Column] ) ) RETURN MAXX ( FILTER ( _table2, [Product Code] = _Product_Code && [Month] = _Month ), [Column] + [Column2] )3.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
Do you mean that the data is stored in 7 different tables? Can you share simple data or .pbix files without sensitive data?
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou