Detect if a sending is early/late/same over n transports

[qwertzuiop]

Hello dear PowerBI-Community

Currently I am in great need of your support.

As shown below is a table showing the messages about a timechange in transports.

A transport always has a unique transportId.

I would like to be able to make a statement as to whether a transport is finally (last timeNew of the transport) earlier, later or at the same time as originally (first timeOld of the transport). These cases are highlighted in color.

The gray column is the actual solution I am looking for.

Any ideas?

transportId	created	timeOld	timeNew	EarlySameLate
890	2019-09-28T19:00:11	2019-10-03T14:00	2019-10-01T14:00	Same
890	2019-09-30T07:00:12	2019-10-01T14:00	2019-10-02T14:00	x
890	2019-09-30T09:45:11	2019-10-02T14:00	2019-10-03T14:00	x
345	2019-10-01T05:30:28	2019-10-01T12:30	2019-10-03T12:30	Late
345	2019-10-01T15:30:16	2019-10-03T12:30	2019-10-02T12:30	x
345	2019-10-01T17:15:21	2019-10-02T12:30	2019-10-03T12:30	x
345	2019-10-01T17:30:21	2019-10-03T12:30	2019-10-02T12:30	x
345	2019-10-02T06:30:10	2019-10-02T12:30	2019-10-03T12:30	x
345	2019-10-02T08:15:19	2019-10-03T12:30	2019-10-03T08:25	x
567	2019-10-19T08:30:09	2019-10-22T12:30	2019-10-23T12:30	Early
567	2019-10-19T09:45:09	2019-10-23T12:30	2019-10-21T12:30	x
159	2019-10-23T04:15:20	2019-10-23T07:30	2019-10-23T15:30	Late
123	2019-10-29T11:45:12	2019-10-30T14:00	2019-11-04T11:30	Late
123	2019-10-29T19:45:15	2019-11-04T11:30	2019-10-31T14:00	x
123	2019-10-29T20:00:22	2019-10-31T14:00	2019-11-04T11:30	x
123	2019-10-30T07:00:15	2019-11-04T11:30	2019-10-31T14:00	x
123	2019-10-30T12:45:15	2019-10-31T14:00	2019-11-04T11:30	x
123	2019-10-31T06:30:10	2019-11-04T11:30	2019-10-31T14:00	x
123	2019-11-01T08:00:18	2019-10-31T14:00	2019-11-01T14:00	x

Thank you so much for your support.

Cheers!

qwertzuiop

[v-xuding-msft · Accepted solution]

Hi @qwertzuiop ,

I created a sample using calculated column. Please have a try. 

Column =
VAR min_date =
    CALCULATE (
        MIN ( 'Table'[created] ),
        ALLEXCEPT ( 'Table', 'Table'[transportId] )
    )
VAR max_date =
    CALCULATE (
        MAX ( 'Table'[created] ),
        ALLEXCEPT ( 'Table', 'Table'[transportId] )
    )
VAR a =
    CALCULATE (
        MAX ( 'Table'[timeOld] ),
        ALLEXCEPT ( 'Table', 'Table'[transportId] ),
        FILTER ( 'Table', 'Table'[created] = min_date )
    )
VAR b =
    CALCULATE (
        MAX ( 'Table'[timeNew] ),
        ALLEXCEPT ( 'Table', 'Table'[transportId] ),
        FILTER ( 'Table', 'Table'[created] = max_date )
    )
RETURN
    SWITCH ( TRUE (), a > b, "Early", a < b, "Late", a = b, "Same" )

Best Regards,

Xue Ding

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