Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
Hi @Rohit_terkar ,
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 @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.
Hi @Rohit_terkar ,
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
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.
https://docs.google.com/spreadsheets/d/1RCBxYYn91yScKWCEDVfI0mQILqUME2lE/edit?gid=1879817136#gid=187...
Hi @Rohit_terkar ,
When I clicked the link, it said I don't have permission.
Best Regards,
Wenbin Zhou
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 ?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |