Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Gorgmaster
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.

machinepartEnd TimeStart Time
1107:30Blank
2207:2907:22
1307:3707:30
1407:4407:37
2507:3607:29
1607:5107:44
2707:22Blank
1807:5807: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

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?

View solution in original post

4 REPLIES 4
sturlaws
Resident Rockstar
Resident Rockstar

Hi,


try this

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

Hey,

thanks for the reply.

 

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.

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?

That's it!

 

Thanks a lot!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.