Future Inventory based on Current Inventory, Forecasted Sales and Future POs

[Pogi_Shane]

been struggling to come up with a measure that calculates the running balance of inventory. Below is a table with the Column 'What i expect" and what i am trying to solve for. it basically takes "sum of on hand" +" Sum of remaining open" - "Sum of Sum of total units" = "What i expect" for December. January then takes the ending balance of December and adds "sum of Remaining Open" - "Sum of Sum of total units" = What i expect.

Year	Month	Sum of On Hand	Sum of Remaining Open	Sum of SUM of Total Units	What I Expect
2024	December	194,212		73,788	120,424
2025	January		50,000	68,679	101,745
2025	February		25,000	81,021	45,724
2025	March		80,000	84,099	41,625
2025	April		50,000	91,693	(68)
2025	May		25,000	144,007	(119,075)
2025	June			96,901	(215,976)
2025	July			103,536	(319,512)
2025	August			108,225	(427,737)
2025	September			110,633	(538,370)
2025	October			104,483	(642,853)
2025	November			190,708	(833,561)

I created this DAX measure but its not getting the previous months ending total to then be the begining balance of the formula

Projected Inventory = SUMX( Netsuite_Inventory, Netsuite_Inventory[On Hand] ) + SUMX(Open_PO, Open_PO[Remaining Open]) - SUMX(Forecast_Info, Forecast_Info[SUM of Total Units])

any help would be really apprecaited! 

Year	Month	Sum of On Hand	Sum of Remaining Open	Sum of SUM of Total Units	Projected Inventory
2024	December	194,212		73,788	120,424
2025	January		50,000	68,679	(18,679)
2025	February		25,000	81,021	(56,021)
2025	March		80,000	84,099	(4,099)
2025	April		50,000	91,693	(41,693)
2025	May		25,000	144,007	(119,007)
2025	June			96,901	(96,901)
2025	July			103,536	(103,536)
2025	August			108,225	(108,225)
2025	September			110,633	(110,633)
2025	October			104,483	(104,483)
2025	November			190,708	(190,708)

[Anonymous · Accepted solution]

Hi, @Pogi_Shane 

Thanks for @Kedar_Pande's reply. You can try this dax to achieve your need.

Add all = 
VAR _onHand =
    SELECTEDVALUE ( Netsuite_Inventory[On Hand] )
VAR _remainingOpen =
    SELECTEDVALUE ( Netsuite_Inventory[Remaining Open] )
VAR _units =
    SELECTEDVALUE ( Netsuite_Inventory[Sum of SUM of Total Units] )
RETURN
    Netsuite_Inventory[On Hand] + Netsuite_Inventory[Remaining Open] - Netsuite_Inventory[Sum of SUM of Total Units]

Cumulative value = 
VAR _index =
    MAX ( Netsuite_Inventory[Index] )
VAR _ACCUMULATED =
    CALCULATE (
        SUM ( Netsuite_Inventory[Add all] ),
        FILTER ( ALL ( Netsuite_Inventory ), Netsuite_Inventory[Index] <= _index )
    )
RETURN
    _ACCUMULATED

Best Regards,
Yang
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.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Kedar_Pande]

@Pogi_Shane 

Corrected DAX Measure:

Projected Inventory =
VAR CurrentMonth =
SELECTEDVALUE(Netsuite_Inventory[Month])
VAR CurrentYear =
SELECTEDVALUE(Netsuite_Inventory[Year])
VAR PriorBalance =
CALCULATE(
SUMX(Netsuite_Inventory, Netsuite_Inventory[What I Expect]),
FILTER(
ALL(Netsuite_Inventory),
Netsuite_Inventory[Year] * 12 + Netsuite_Inventory[Month] <
CurrentYear * 12 + CurrentMonth
)
)
VAR OnHand = SUM(Netsuite_Inventory[Sum of On Hand])
VAR RemainingOpen = SUM(Open_PO[Remaining Open])
VAR TotalUnits = SUM(Forecast_Info[SUM of Total Units])
RETURN
IF(
CurrentMonth = "December" && CurrentYear = 2024,
OnHand + RemainingOpen - TotalUnits,
PriorBalance + RemainingOpen - TotalUnits
)

Replace What I Expect with the actual column or calculated measure for prior months.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn