0

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.

Vlogs Bengali
  • 85
  • 1
  • 13
  • Why it is X for id 4 and 7? – mkRabbani Jan 01 '21 at 08:43
  • Because there is higher values between 106 and 107.I want to see what is the last id after which the value was recovered. – Vlogs Bengali Jan 01 '21 at 09:53
  • Can you explain the logic bit more? – mkRabbani Jan 01 '21 at 10:08
  • Consider a value 114.I want to know what is the latest data in the series after which the value went higher.I.e recovery period.Number of rows/taken to recover the price. It will search for the nearest closest value which is lower than 114.Is it understandable.(just ignore the for the time being,consider value of X as ID 7) – Vlogs Bengali Jan 01 '21 at 10:46
  • Is there any condition like - ID > current_id? – mkRabbani Jan 01 '21 at 10:51
  • What do you mean by "after"? there is no timestamp and the iDs are not sorted in a progressive way: the first rows shows the last_id is greater than current ID – sergiom Jan 01 '21 at 15:24

1 Answers1

0

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-

enter image description here

mkRabbani
  • 16,295
  • 2
  • 15
  • 24