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
Icon for Community Support rankCommunity Support
1 year ago

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.