Based on this workbook, you appear to be familiar with window calculations. I believe what you are after is LOOKUP.
Gives you the value in RecoveryUpDown from the previous row in the [window] partition.
Looking for the next value rather than previous value. Also lookup doesn't work for the same column
Change -1 to 1 to make it next value instead of previous. The int parameter in that calc is the offset, so you can navigate to whatever row within the partition you want, dynamically if you start to introduce first(), last() and size().
You can use the existing RecoveryUpDown as a helper calculation (rename it if you want) that does not need to be shown. Then use the lookup against that to do your conditional logic and actually return the result.
However, LOOKUP doesn’t allow you to navigate on the same column. This is a recursive function - you cant write it with helper column.
LOOKUP([Field],[Off-set]), LAST() and FIRST() are your friends.
Setting offset to -1(i.e. LOOKUP([RecoveryUpDown],-1) will give you 1 set back/previous RecoverUpDown value), 0( look current value) and 1(1 set forward/next value).
LOOKUP([RecoveryUpDown],0) is quite handy as is self looking, namely looking at the current value.
FIRST()=0 will get you the first/old value and Last()=0 will get you the last/earliest value.
You are almost there, just a little bit of tweaking your current formulas. I'm not entirely sure if you only want to make the next null row "Down" if the previous row is "Down" and there is a local minima. Or you would want to recursively fill every null row with the previous value until a different non-null value is encountered. I have created solution for each option below.
Option 1. Create calculated field [RecoveryUpDown_v2]
IF ISNULL([RecoveryUpDown]) AND LOOKUP([RecoveryUpDown],-1) = "Down" AND NOT ISNULL([Local Minimum]) THEN "Down"
This formula fills the next null row post local minima with "Down" if the previous value is Down.
Option 2. Create calculated field [RecoveryUpDown_v3]
IF ISNULL(LOOKUP([RecoveryUpDown],0)) THEN PREVIOUS_VALUE([RecoveryUpDown])
This formula recursively assigns a null row with the value of the previous non-null row.
Let me know if this meets your requirement or if you will want me to tweak the formulas further. See attached workbook.
Hope this helps.
Example Next Value_OO.twbx 63.6 KB