Ending Inventory using January value and carrying forward the answer as beginning inventory MOM

[Anonymous]

I have a data set and I'm trying to get the following output in a BI visual. I have been doing a filter formula to get the beginning inventory for January, but that's where it goes south. Help would be much appreciated. The table below, the ending Inventory for Actuals is what I am aiming for with my BI table. This is just one of 4 divisions that I am working on with the overall report. Keep in mind that I have to use the starting inventory from another column table, then add the inbound, then subtract the outbound to get my closing balance for each month for each division. 

Beginning Inventory for January 2024 (keep in mind this isn't stated in the actuals table)	28,846
	1/1/2024	1/1/2024	2/1/2024	2/1/2024	3/1/2024	3/1/2024	4/1/2024	4/1/2024	5/1/2024	5/1/2024	6/1/2024	6/1/2024	7/1/2024	7/1/2024	8/1/2024	8/1/2024	9/1/2024	9/1/2024	10/1/2024	########	11/1/2024	########	12/1/2024	########
	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual
Inbound	2,518	79	80	80	-	-	575	71	421	210	212	1,046	1,968	180	1,466	1,986	512		-		117		-
Outbound	3,525	3,909	1,656	1,420	1,370	3,466	1,014	3,185	1,412	2,550	2,092	3,906	1,934	2,757	1,395	2,825	1,890		1,323		1,203		1,109
Ending Inventory for Actuals (this is an output as a result of the above math).		25,016		27,506		25,380		25,732		26,506		25,986		26,269		28,007

[parry2k]

@Anonymous sounds good - although the DAX function I gave will take care of it. Good luck!

[parry2k · Accepted solution]

@Anonymous you can take advantage of openingbalanceyear DAX function, read more about it here: OPENINGBALANCEYEAR function (DAX) - DAX | Microsoft Learn

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

[Anonymous]

Thank you for your response. The first issue, is the ending inventory, I included what I should see, but that's something I need the formula for and the premise is that I need to take the beginning inventory, add the inbound, subtract the outbound to get the answer. I am currently watching a closing balance youtube option as the opening balance isn't working for me. 

[Anonymous]

I do have a date table already incorported. I'm currently reviewing Amit Chandak's youtube video Abstract Thesis 92 and seeing if I can use that for this solution as well.