Forum Discussion

DemingPDCA's avatar
DemingPDCA
Helper II
3 years ago
Solved

Find First Instance After a different instance

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

IndexDue DateRunning TotalCheckStatus
709167/5/202312Positive
709177/12/202311Positive
709187/18/202310Positive
709197/21/20239Positive
709207/28/20238Positive
709218/1/2023-2Negative
709228/3/2023-3Negative
709238/4/20232Positive
709248/10/20231Positive
709258/18/20234Positive
709268/18/20233Positive
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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

     

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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

     

  • DemingPDCA's avatar
    DemingPDCA
    Helper II
    3 years ago

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