Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Rohit_terkar
Helper I
Helper I

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    
 InventoryPO(+)SC(+)SO(-)BOM(-)BalancePO(+)SC(+)SO(-)BOM(-)Balance
AB1011010105520520201015
AB1022051010520510101510
1 ACCEPTED SOLUTION

Hi @Rohit_terkar ,

Please see the attachment for details.

vzhouwenmsft_0-1721382400934.png

Best Regards,
Wenbin Zhou

 

View solution in original post

9 REPLIES 9
Rohit_terkar
Helper I
Helper I

Thank you @v-zhouwen-msft 

v-zhouwen-msft
Community Support
Community Support

Hi @Rohit_terkar ,

The Table data is shown below:

vzhouwenmsft_0-1721203112329.png

Please follow these steps:

1.Add index column after grouping

vzhouwenmsft_1-1721203278583.png

Table.AddIndexColumn([Column],"Index",1)

vzhouwenmsft_2-1721203336625.png

vzhouwenmsft_3-1721203436311.png

vzhouwenmsft_4-1721203473432.png

vzhouwenmsft_5-1721203511741.png

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

vzhouwenmsft_6-1721203620130.png

 

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?

vzhouwenmsft_0-1721293185600.png

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

You can open now.
Regards,
Rohit
 

Hi @Rohit_terkar ,

Please see the attachment for details.

vzhouwenmsft_0-1721382400934.png

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 ?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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