Forum Discussion
DemingPDCA
3 years agoHelper II
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 ...
- 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.
- 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
3 years agoHelper II
You - have just saved me SO MUCH time and frustration!!! I cannot thank you enough!!!
- rubayatyasmin3 years agoCommunity Champion
Happy to help.
Appreciate the kudos. 👍