Forum Discussion
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 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 |
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
4 Replies
johnt75Super User
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-ssriganeshCommunity Support
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.
- 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,
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.