Create a measure comparing two values of different rows and columns if they meet two conditions

[jsanchezm]

Hi,

I'm stuck trying to create a measure that compares values from two columns of two different rows, but from the same table, if, and only if, they meet two conditions:

In example: if the two rows are from the same date (data in my language) and it is also from the same bus, compare the H_FIN from the first one to the H_INI of the second one. For the data included in the next image, the first row wouldn't be compared to any other row, as it meets the date condition, but not the bus one (it is the only one with the bus 12377). The second row wouldn't need to be compared, as it has not any previous row of that meets the two condition criteria, but the third one as it is from the same date and bus of the previous one, would need to calculate and get the value "01:55" as it is the duration between the H_FIN of the first row and H_INI of the second one.

I've seen some posts talking about similar things, but they don't seem to fit with my problem or, at least, I can't see how to work the examples for my problem.

Any hints, please? Thanks in advance for your help.

[v-yanjiang-msft · Accepted solution]

Hi @jsanchezm ,

According to your description, here's my solution.

Create a measure.

Diff =
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = MAX ( 'Table'[DATE] )
                && 'Table'[BUS] = MAX ( 'Table'[BUS] )
                && 'Table'[IDTRAM] < MAX ( 'Table'[IDTRAM] )
        ),
        'Table'[H_FIN]
    )
RETURN
    IF ( _Pre = BLANK (), BLANK (), MAX ( 'Table'[H_INI] ) - _Pre )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.