Find First Instance After a different instance

[DemingPDCA]

Hopefully I can explain this. I'm looking to find two things. First I need a measure that first the first Positive AFTER the first negative, then I need to find how negative did we go before we went positive?

Examples:

In this example - the stock out date is 7/31 (calculated as: 

CALCULATE(Min('Inventory Watch'[Due Date]), FILTER('Inventory Watch', 'Inventory Watch'[CheckStatus] = "Negative") )

I would also liked to return the first positive after the stockout date (8/4), and was is the most negative we went during that date range (-5)


Example 2:

Stock out Date = 8/1

If copy and past needed:First Positive After Stock Out = 8/4
Most Negative = -3


if copy paste needed

Index	Due Date	Running Total	CheckStatus
70916	7/5/2023	12	Positive
70917	7/12/2023	11	Positive
70918	7/18/2023	10	Positive
70919	7/21/2023	9	Positive
70920	7/28/2023	8	Positive
70921	8/1/2023	-2	Negative
70922	8/3/2023	-3	Negative
70923	8/4/2023	2	Positive
70924	8/10/2023	1	Positive
70925	8/18/2023	4	Positive
70926	8/18/2023	3	Positive

[DemingPDCA]

You - have just saved me SO MUCH time and frustration!!! I cannot thank you enough!!!

[rubayatyasmin]

Happy to help. 

Appreciate the kudos. 👍

[rubayatyasmin · Accepted solution]

Hi, @DemingPDCA 

In this case, you can use a similar approach as the one you used for finding the stock out date.

1. To find the first Positive AFTER the first negative, you need to get the Min Due Date where CheckStatus is Positive, but only from the rows after the first row where CheckStatus is Negative. example

CALCULATE (
    MIN ( 'Inventory Watch'[Due Date] ),
    FILTER (
        ALL ( 'Inventory Watch' ),
        'Inventory Watch'[Due Date] > CALCULATE (
            MIN ( 'Inventory Watch'[Due Date] ),
            FILTER ( 'Inventory Watch', 'Inventory Watch'[CheckStatus] = "Negative" )
        )
            && 'Inventory Watch'[CheckStatus] = "Positive"
    )
)

To find the most negative value between the first Negative and the next Positive, you would use a formula like:

CALCULATE (
    MIN ( 'Inventory Watch'[Running Total] ),
    FILTER (
        ALL ( 'Inventory Watch' ),
        'Inventory Watch'[Due Date] >= CALCULATE (
            MIN ( 'Inventory Watch'[Due Date] ),
            FILTER ( 'Inventory Watch', 'Inventory Watch'[CheckStatus] = "Negative" )
        )
            && 'Inventory Watch'[Due Date] <= CALCULATE (
                MIN ( 'Inventory Watch'[Due Date] ),
                FILTER (
                    ALL ( 'Inventory Watch' ),
                    'Inventory Watch'[Due Date] > CALCULATE (
                        MIN ( 'Inventory Watch'[Due Date] ),
                        FILTER ( 'Inventory Watch', 'Inventory Watch'[CheckStatus] = "Negative" )
                    )
                        && 'Inventory Watch'[CheckStatus] = "Positive"
                )
            )
    )
)

These DAX formulae will help you find the first positive date after the first negative one, and the minimum (most negative) 'Running Total' value between the first negative date and the next positive one.

note: youmight need some adjustment