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

     

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             -