Forum Discussion

gastonguy's avatar
gastonguy
Frequent Visitor
9 years ago

previous

Hello,   I have a table with different same object_number but the objects evolves over time and we can track it with the object_line_number. And every evolution of the object has a status. I want ...
austinsense's avatar
austinsense
Impactful Individual
9 years ago

Give this a try

 

= VAR objectnum = tablename[object_number],
      status = tablename[old_status],
      linenum = tablename[object_line_number]

RETURN

// We're going to iterate through the table, get an answer for each row, and then add them up at the end
SUMX(tablename,

// We'll start with the modification date for the current row

date_modification -

// then get and subtract the modification date for the previous record
CALCULATE(MAX(date_modification),
      FILTER(ALL(tablename),
           object_number = objectnum &&
           old_status = status &&
           object_line_number < linenum
      )
))