Forum Discussion

mmossy's avatar
mmossy
Regular Visitor
1 year ago
Solved

Forecasted Inventory while staying above zero if negative

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 ...
"DAX " "DAX help" 'Need help' 'DAX'
"Power BI"
"Power Pivot"
  • johnt75's avatar
    johnt75
    1 year ago

    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
v-ssriganesh's avatar
v-ssriganesh
Community Support
1 year ago

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.