I currently have an issue with trying to forecast my stock on hand by SKU by month. I have my current SOH by SKU as well as forecasted sales & purchases which i have summarised into a net movements by month per SKU. The below tables are basic examples of my data and the 3rd table is a summary of how i want the calculations to work. Basically i need a running total of forecasted SOH where the SKU SOH for any given month returns 0 if the forecasted SOH is less than 0, but i would like the negative amount to carry over as open sales orders. So with SKU A, it returns 0 at 31/03/2025 because it is forecasted to be less than 0, but at 30/04/2025 it returns 7 because Opening Stock + Forecast Movements + Net units carried over = 0 + 10 - 3 = 7 DateSKUStock on Hand28/02/2025A228/02/2025B8 MonthYrSKUNet Forecast Movements31/03/2025A-530/04/2025A1031/03/2025B130/04/2025B5 DateSKUOpening SOHNet Forecast MovementsForecasted SOH31/03/2025A2-5031/03/2025B81930/04/2025A010730/04/2025B9514

Try Forecasted SOH = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR Result = SUMX ( 'Product', VAR ForecastValue = CALCULATE ( SUM ( Forecast[Net forecast movements] ), 'Date'[Date] <= CurrentDate ) VAR Total = 'Product'[Stock on hand] + ForecastValue VAR Result = MAX ( Total, 0 ) RETURN Result ) RETURN Result

Forecasted Inventory while staying above zero if negative

Forum Discussion

Related content

Inventory Forecast

Inventory forecast

Inventory Forecast Isuue

Cumulative inventory Forecast

Inventory forecasting