Find First Positive AFTER first negative

[DemingPDCA]

I'm trying to create an inventory model that shows things we expect to go negative inventory on based on incoming purchase orders vs demand. I've been able to create a way to find the first date we go negative - but I'm struggling to get something working to show when we go positive.  

IN this case below - we have 32 on hand, on 7/26 we have a demand of 40 so we will go negative that day for the first time. and then we don't get more in until 9/1, at which point we will be back in the green. Any suggestions on calculating first positive after negative date? Current calculations and screenshots below

Total Due Qty = Sum('Inventory Watch'[Due Qty])

Due Qty running total in Due Date = 
VAR PartNumber = SELECTEDVALUE('Inventory Watch'[Part Number])
VAR QOH = CALCULATE(Max('Part Master'[Quantity On Hand]), FILTER('Part Master', 'Part Master'[Part Number] = PartNumber))

RETURN
QOH + 
CALCULATE(
	[Total Due Qty]
	, FILTER(
		ALLEXCEPT('Inventory Watch', 'Part Master'[Part Number])
        , 'Inventory Watch'[Due Date] <= MAX('Calendar'[Date])))

Find First Negative = 
VAR _date = min ('Calendar'[Date]) 
VAR _balance = CALCULATE ([Due Qty running total in Due Date], 'Inventory Watch'[Due Date] <= _date)
VAR _check = IF (_balance < 0, _date)

RETURN 
_check

[DemingPDCA]

The data in the table shown is an appended table. The first one is Supply (all incoming Purchase orders currently scheduled and when they are scheduled to come in) and other table is demand based on all Work Orders and what are required to build them. In the case below we go negative on line 28 (7/19), but then immediately would go positive again on line 29 (7/20). I'm just not sure how to say look at the line above and IF it was the first negative then tell me when I reach the first positive after that first negative. (Won't lie - only got this far because BI Gorilla -BI Gorilla - YouTube- made a fantastic youtube video on how to do it step by step!)

The table has ALL part numbers sorted by date and then calculated a running total with a function

= (RT_Name as text, MyTable as table, RT_ColumnName as text) =>

let
Source = MyTable,
BuffValues = List.Buffer(Table.Column(MyTable, RT_ColumnName)),
RunningTotal = List.Generate(() => [RT = BuffValues{0}, RowIndex = 0],
each [RowIndex] < List.Count(BuffValues),
each [RT = List.Sum({[RT], BuffValues{[RowIndex] + 1}}),
RowIndex = [RowIndex] + 1],
each [RT]),
Consolidation = Table.FromColumns(
Table.ToColumns(Source) & {Value.ReplaceType(RunningTotal, type {Int64.Type})},
Table.ColumnNames(Source) & {RT_Name})
in
Consolidation

[lbendlin]

Please provide that sample data in usable form (not as a screenshot)

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[DemingPDCA]

Does this work - can't seem to get a pbix file to work correctly. Looking for a way to return 7/14/2023 (first positive after first negative) and -2 max negative it went during that time period

Part Number	Due Qty	Due Date	On Hand	Part Wise Running Total	Updated Running Total	Index	CheckStatus
B	-5	12/21/2022	7	-5	2	9089	Positive
B	-1	6/20/2023	7	-6	1	9090	Positive
B	-1	6/23/2023	7	-7	0	9091	Zero
B	-1	6/29/2023	7	-8	-1	9092	Negative
B	-1	7/5/2023	7	-9	-2	9093	Negative
B	7	7/14/2023	7	-2	5	9094	Positive
B	-1	7/14/2023	7	-3	4	9095	Positive

[lbendlin]

Technically this is easy - compare two adjacent entries and trigger when the prior is negative and the new is positive.  But how are you planning to include that in a forecast?  Are you estimating the 9/1 date and volume?