powerbi - Calculate last lowest value using DAX

Question

Data1:

ID Value 
2   114    
3   108    
4   107    
5   113    
6   112    
7   106    
8   109  

Data Required In Power BI(DAX):

ID Value last_recovery_id
2   114    5
3   108    4
4   107    X
5   113    6
6   112    7
7   106    X
8   109  

Basically I want to know the last id after which the price went higher.

Answer 1

You can try this below measure-

last_recovery_id = 
var current_row_value = min(your_table_name[Value])
var current_row_id = min(your_table_name[ID])
var previous_max_value = 
CALCULATE(
    MAX(your_table_name[Value]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Value] < current_row_value
        && your_table_name[ID] > current_row_id
    )
)

var prevous_id =  
CALCULATE(
    MAX(your_table_name[id]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Value] = previous_max_value
        && your_table_name[ID] > current_row_id
    )
)

RETURN IF(prevous_id = BLANK(),"X", prevous_id)

The output is-