Current Ending inventory Calculation followed by cumulative calculation for upcoming months

[PBI_1107]

Hello Community,
I am having trouble finding the DAX Calculation for the Ending Inventory. The calculation goes like this 
1. Current_Ending_Inventory = (Inventory + Transit + Schedules) - ((Forecast * Reference number[)+ Future_orders + Backorders)
This calculation gives me the Ending Inventory for the current Month ( Oct 2023), 
I want a Calculation where for the following month will be like :
Nov 23 Ending Inventory= [Oct 23 Ending Inventory + Oct 23 Schedules] - [Oct 23 Forecast + Oct 23 Future_orders]

Similarly Dec 23 Ending Inventory = [Nov 23 Ending Inventory + Nov 23 Schedules] - [Nov 23 Forecast + Nov23 Future_orders]
So on and so forth.....
Here is the Table for your better understanding:
This table is Based on ID_NO and SHIP_MONTH

[PBI_1107]

Hello @Alef_Ricardo_ , 
I thank you for responding back to the post immediately, 
I have a question regarding the calcualtion you have given. So for "VAR PreviousEndingInv" The [EndingInventory] is it the CurrentEndingInventory or a different calculation, Becuase the values are showing different results.
Please let me know
thanks.

[Alef_Ricardo_]

To calculate the ending inventory for each month, you can create a DAX measure that accumulates the inventory changes over time. You'll need to use a combination of measures to calculate the ending inventory for each month based on the specified formula. Below is the DAX measure for your requirement:

```DAX
Ending Inventory =
VAR CurrentMonth = MAX('YourTable'[SHIP_MONTH])
VAR PreviousMonth = MAX('YourTable'[SHIP_MONTH]) - 1
VAR CurrentEndingInventory =
CALCULATE(
[Inventory] + [Transit] + [Schedules] - ([Forecast] * [Reference number] + [Future_orders] + [Backorders]),
'YourTable'[SHIP_MONTH] = CurrentMonth
)
VAR PreviousEndingInventory =
CALCULATE(
[Ending Inventory],
'YourTable'[SHIP_MONTH] = PreviousMonth
)
RETURN
IF(
CurrentMonth = MIN('YourTable'[SHIP_MONTH]),
CurrentEndingInventory,
PreviousEndingInventory + [Schedules] - ([Forecast] + [Future_orders])
)
```

Make sure to replace `'YourTable'` with the actual name of your table, and ensure that your table has the necessary columns: SHIP_MONTH, Inventory, Transit, Schedules, Forecast, Reference number, Future_orders, and Backorders.

This measure calculates the ending inventory for each month based on the formula you specified, taking into account the previous month's ending inventory. The IF statement checks if the current month is the minimum month in your table (i.e., the starting point), and if so, it uses the current month's ending inventory. Otherwise, it calculates the ending inventory based on the previous month's ending inventory, schedules, and the forecast and future orders for the current month.

[PBI_1107]

Hello @Alef_Ricardo_ ,
I tired the calculation you gave and the value for the Oct month was correct but for the months after Oct 23 were showing different Values: 

But this is How the Actual result should look like:

The Calculation with the "PreviousEndingInv" in the Return function gives the accurate result for Oct 23 month but for Nov, dec and so on the visual is showing different values:
This is the calculation I used :
''' DAX

EndingInventory =

VAR CurrentMonth = MAX('New_Table'[SHIP_MONTH].[Date])

VAR PrevMonth = MAX('New_Table'[SHIP_MONTH].[Date])-1

VAR CurrentEndingInv =

([Inventory_Sum] + [Transit_sum] + [Schedules_Sum]) - (([FORECAST]*[Reference number])+[FDO_CPRO_sum] + [Backorders_sum1])

VAR CurrentEndingInv1 =

            CALCULATE(

            CurrentEndingInv , 'New_Table'[SHIP_MONTH] = CurrentMonth)

VAR PreviousEndingInv =

CALCULATE(CurrentEndingInv, 'New_Table'[SHIP_MONTH].[Date] = PrevMonth)

    Return PreviousEndingInv

''''
Please let me know if there is any changes i need to make.
Thanks,
PBI_1107