Beginning and Ending Inventory Balance

[nickal251]

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 · Accepted solution]

Thanks for the reply from lbendlin.

Hi @nickal251 ,

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]

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

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

[nickal251]

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

[nickal251]

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	-