Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Beginning and Ending Inventory Balance

Hello all,

 

I am trying to recreate the table below in PBI. I have models with measures for current inventory, forecasts, orders shipped, production, and transfers. I need to create a table that will pull from all the models and populate the table. The catch is that each column needs to sum and project a forecasted ending inventory. Also, the first column (current month) will update with orders shipping, and production/transfer receipts as the month progresses. Thanks for your attention.

 

Bonus points for DOS row!!

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin.

     

    Hi Anonymous ,

    Based on the data you said you provided, I unpivoted  your tables and created the relationship shown below:

     

    After understanding your logic, I adjusted the rows and columns and hopefully this will meet your needs as well，here are the measures l created:

     

    1.Perform the calculations in the figure:

     

    sumsales = SUM('Current Inv'[Inventory])+SUM('Current Month Shipments'[ Month Shipments])-SUM('Forecast'[Forecast])+SUM('Monthly Production Transfer Rec'[Receipts])

     

     

    2.Calculate total sales up to the current selection month:

     

    End of month balance =
IF(NOT(SUM('Current Inv'[Inventory])=BLANK()&&SUM('Current Month Shipments'[ Month Shipments])=BLANK()&&SUM('Forecast'[Forecast])=BLANK()&&SUM('Monthly Production Transfer Rec'[Receipts])=BLANK()),
SUMX(FILTER(ALL('Month'),'Month'[YearMonth]<=SELECTEDVALUE('Month'[YearMonth])),[sumsales]))

     

     

    3.Get the unrestricted inventory similar to the requirement figure:

     

    Inventory =
IF(NOT(ISBLANK(SUM('Current Inv'[Inventory]))),SUM('Current Inv'[Inventory]),
IF(NOT(SUM('Current Inv'[Inventory])=BLANK()&&SUM('Current Month Shipments'[ Month Shipments])=BLANK()&&SUM('Forecast'[Forecast])=BLANK()&&SUM('Monthly Production Transfer Rec'[Receipts])=BLANK()),
SUMX(FILTER(ALL('Month'),'Month'[Index] <= SELECTEDVALUE('Month'[Index]) -1),[sumsales])))

     

     

    The final result is as follows:

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from lbendlin.

     

    Hi Anonymous ,

    Based on the data you said you provided, I unpivoted  your tables and created the relationship shown below:

     

    After understanding your logic, I adjusted the rows and columns and hopefully this will meet your needs as well，here are the measures l created:

     

    1.Perform the calculations in the figure:

     

    sumsales = SUM('Current Inv'[Inventory])+SUM('Current Month Shipments'[ Month Shipments])-SUM('Forecast'[Forecast])+SUM('Monthly Production Transfer Rec'[Receipts])

     

     

    2.Calculate total sales up to the current selection month:

     

    End of month balance =
IF(NOT(SUM('Current Inv'[Inventory])=BLANK()&&SUM('Current Month Shipments'[ Month Shipments])=BLANK()&&SUM('Forecast'[Forecast])=BLANK()&&SUM('Monthly Production Transfer Rec'[Receipts])=BLANK()),
SUMX(FILTER(ALL('Month'),'Month'[YearMonth]<=SELECTEDVALUE('Month'[YearMonth])),[sumsales]))

     

     

    3.Get the unrestricted inventory similar to the requirement figure:

     

    Inventory =
IF(NOT(ISBLANK(SUM('Current Inv'[Inventory]))),SUM('Current Inv'[Inventory]),
IF(NOT(SUM('Current Inv'[Inventory])=BLANK()&&SUM('Current Month Shipments'[ Month Shipments])=BLANK()&&SUM('Forecast'[Forecast])=BLANK()&&SUM('Monthly Production Transfer Rec'[Receipts])=BLANK()),
SUMX(FILTER(ALL('Month'),'Month'[Index] <= SELECTEDVALUE('Month'[Index]) -1),[sumsales])))

     

     

    The final result is as follows:

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

     

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    As long as there are no conditionals this is achievable in DAX.  Have you tried creating a measure yet?

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Current Inv

       

                              100    362,000
                               101      39,900
                              102      20,425
                              103             -  
                              104     157,500
                              105    470,000
                              106             -  
                              107             -  
                              108     217,645
                              109             -  

       

      Forecast

       

                        100     130,000    130,000     130,000    153,500    140,000    153,500    198,000     191,500     191,500
                         101             -               -               -               -        31,500      31,500      33,250      31,500      31,500
                        102           961      18,939        4,902        4,997        3,936        2,561        3,744        3,436        3,664
                        103             -               -               -               -               -               -               -               -        12,000
                        104      50,000      50,000      50,000             -               -               -               -               -               -  
                        105     120,000     110,000     113,488      60,001    145,000      58,750     150,001      78,750             -  
                        106             -               -               -      100,000             -               -               -               -               -  
                        107     114,000             -               -               -               -               -        19,543    342,000    607,391
                        108             -               -               -               -               -               -               -               -               -  
                        109             -               -               -               -          1,508             -               -               -               -  

       

      Current Month Shipments

       

                                            100             -  
                                             101             -  
                                            102           100
                                            103             -  
                                            104             -  
                                            105      18,750
                                            106             -  
                                            107             -  
                                            108             -  
                                            109             -  
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Monthly Production/Transfer Receipts

       

                                                                100    199,250    182,646    199,250   182,646    149,438    149,438
                                                                 101      25,375       21,146      23,260     23,260      25,375       16,917
                                                                102        3,875        3,875        3,875       5,329        5,329        3,875
                                                                103        1,200          900          800          900          800         1,100
                                                                104      12,500       11,250      13,750      11,250      13,750      13,750
                                                                105     107,166     117,883    128,599     85,733      85,733     107,166
                                                                106        6,667        7,500        9,167       8,333        9,167        8,333
                                                                107     101,662     101,662    139,786   127,078     114,370    152,494
                                                                108             -               -               -              -               -               -  
                                                                109           715          643          643          715           715          643