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 ...
  • rubayatyasmin's avatar
    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