topic Re: Forecasted Inventory while staying above zero if negative in DAX Commands and Tips

Forecasted Inventory while staying above zero if negative

mmossy — Fri, 14 Mar 2025 04:15:32 GMT

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

Re: Forecasted Inventory while staying above zero if negative

johnt75 — Wed, 19 Mar 2025 10:29:24 GMT

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

Re: Forecasted Inventory while staying above zero if negative

v-ssriganesh — Mon, 14 Apr 2025 04:25:45 GMT

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.

Re: Forecasted Inventory while staying above zero if negative

v-ssriganesh — Tue, 22 Apr 2025 17:32:59 GMT

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.

Re: Forecasted Inventory while staying above zero if negative

v-ssriganesh — Fri, 25 Apr 2025 07:11:02 GMT

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.