cancel
Showing results for
Did you mean:
Regular Visitor

## Comparison of Values from different rows

Hey there!

Got a little Problem with some data.

For better understanding here an example:

I've got a table with time stamps of machined parts. The time stamp tells me, when the part was finished. I would like to take the time stamp of the last part as start time for the next part. So I will get a time range, during which time a part was produced. In a second tabel I can compare the time ranges to error messages. That I already solved.

Here is an example for the tabel, the last column should be calculated.

 machine part End Time Start Time 1 1 07:30 Blank 2 2 07:29 07:22 1 3 07:37 07:30 1 4 07:44 07:37 2 5 07:36 07:29 1 6 07:51 07:44 2 7 07:22 Blank 1 8 07:58 07:51

I tried to find the Min-Value of a filtered list where the row of the actual value is excepted. But I didn't get to a solution.

Hopefully someone can help!

1 ACCEPTED SOLUTION
Resident Rockstar

So a slight modification to the code then:

```Start time =
VAR _prevTime = 'Table'[End Time]
VAR _machine = 'Table'[machine]
RETURN
CALCULATE (
MAX ( 'Table'[End Time] );
FILTER ( 'Table'; 'Table'[machine] = _machine && 'Table'[End Time] < _prevTime )
)```

And just to be sure, you are trying to create a calculated column, right?

This works on the sample data you provided. If you have trouble implementing it in your model, perhaps post a more realistic dataset?

4 REPLIES 4
Resident Rockstar

Hi,

try this

```Start time =
CALCULATE (
MAX ( 'Table'[End Time] );
FILTER (
'Table';
'Table'[machine] = EARLIER ( 'Table'[machine] )
&& 'Table'[part] < EARLIER ( 'Table'[part] )
)
)```
Regular Visitor

Hey,

Unfortunately it doesn't work. I've tried the formula with the exact given table in my question. But I got in the start-column the same time as in the end column.

By the way, the part number isn't meant as a rank. it is only a number.

Resident Rockstar

So a slight modification to the code then:

```Start time =
VAR _prevTime = 'Table'[End Time]
VAR _machine = 'Table'[machine]
RETURN
CALCULATE (
MAX ( 'Table'[End Time] );
FILTER ( 'Table'; 'Table'[machine] = _machine && 'Table'[End Time] < _prevTime )
)```

And just to be sure, you are trying to create a calculated column, right?

This works on the sample data you provided. If you have trouble implementing it in your model, perhaps post a more realistic dataset?

Regular Visitor

That's it!

Thanks a lot!

Announcements