Forum Discussion
Ending Inventory using January value and carrying forward the answer as beginning inventory MOM
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 |
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
4 Replies
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 PlaylistThank 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 sounds good - although the DAX function I gave will take care of it. Good luck!