cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Community Support

Please see the attachment for details.

Best Regards,
Wenbin Zhou

9 REPLIES 9
Frequent Visitor

Thank you @v-zhouwen-msft

Community Support

The Table data is shown below:

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 =
_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.

Frequent Visitor

Hi @v-zhouwen-msft ,
Thank you for Solution.
but i dont have the combined data.
every table is different and what you have suggested in first image is combined.

Community Support

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

Frequent Visitor

Yes. Plase find the below link to excel file.
where I have calculated the expected result in excel. but i want to do it same in Bi.

Community Support

When I clicked the link, it said I don't have permission.

Best Regards,
Wenbin Zhou

Frequent Visitor

Hi @v-zhouwen-msft

You can open now.
Regards,
Rohit

Community Support

Please see the attachment for details.

Best Regards,
Wenbin Zhou

Frequent Visitor

Hi @v-zhouwen-msft

I tried to utilise the file and format that you have given but its not fulfilling the outcome i want.
Could you please look into it and help me witha solution according to the changes I have made into a file
Sample Inventory Data.xlsx - Google Sheets

Can you please check and let me know ?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors