Inventory Forecast Isuue

[Rohit_terkar]

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

[Rohit_terkar]

Thank you @v-zhouwen-msft 

[v-zhouwen-msft]

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.

[Rohit_terkar]

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.

[v-zhouwen-msft]

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

[Rohit_terkar]

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

[v-zhouwen-msft]

Hi @Rohit_terkar ,

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

Best Regards,
Wenbin Zhou

[Rohit_terkar]

Hi @v-zhouwen-msft

You can open now.
Regards,
Rohit
 

[v-zhouwen-msft · Accepted solution]

Hi @Rohit_terkar ,

Please see the attachment for details.

Best Regards,
Wenbin Zhou

[Rohit_terkar]

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 ?