Forecasted Inventory while staying above zero if negative

[mmossy]

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

Date	SKU	Stock on Hand
28/02/2025	A	2
28/02/2025	B	8

MonthYr	SKU	Net Forecast Movements
31/03/2025	A	-5
30/04/2025	A	10
31/03/2025	B	1
30/04/2025	B	5

Date	SKU	Opening SOH	Net Forecast Movements	Forecasted SOH
31/03/2025	A	2	-5	0
31/03/2025	B	8	1	9
30/04/2025	A	0	10	7
30/04/2025	B	9	5	14

[v-ssriganesh]

Hi @mmossy,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @mmossy,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @mmossy,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @johnt75 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[johnt75 · Accepted solution]

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