(Advanced) Date formula does work but not for the whole dataset

[qwertzuiop]

Dear power bi community
I guess only advanced users can answer my question. Nevertheless I am looking for help.

As you can see I use the following formula for my data:

EarlyLateSameOnTransport = IF(

[checkFirstTime] = TRUE();

VAR min_date =

CALCULATE (

MIN ( 'Cargo-Push'[created (S)] );

ALLEXCEPT ( 'Cargo-Push'; 'Cargo-Push'[transportId (N)] )

)

VAR max_date =

CALCULATE (

MAX ( 'Cargo-Push'[created (S)] );

ALLEXCEPT ( 'Cargo-Push'; 'Cargo-Push'[transportId (N)] )

)

VAR a =

CALCULATE (

MAX ( 'Cargo-Push'[etaAlt (S)] );

ALLEXCEPT ( 'Cargo-Push'; 'Cargo-Push'[transportId (N)] );

FILTER ( 'Cargo-Push'; 'Cargo-Push'[created (S)] = min_date )

)

VAR b =

CALCULATE (

MAX ( 'Cargo-Push'[eta (S)] );

ALLEXCEPT ( 'Cargo-Push'; 'Cargo-Push'[transportId (N)] );

FILTER ( 'Cargo-Push'; 'Cargo-Push'[created (S)] = max_date )

)

RETURN

SWITCH ( TRUE (); a > b; "Early"; a < b; "Late"; a = b; "Same" );

"x")

What does this formula do? It should check whether a transport (TransportID) - it can occur several times (here 1 or 2) in the end arrives early, late or at the same time as expected.

transportId (N)	created (S)	etaAlt (S)	eta (S)	checkFirstTime	transportIdOccurence	EarlyLateSameOnTransport
12933909	16.09.2019 11:00	17.09.2019 12:30	16.09.2019 12:30	True	2	Same
12933909	17.09.2019 05:00	16.09.2019 12:30	17.09.2019 12:30	False	2	x
12935289	24.09.2019 03:30	24.09.2019 08:30	25.09.2019 08:30	True	2	Late
12935289	24.09.2019 20:00	25.09.2019 08:30	02.10.2019 08:30	False	2	x
12974079	19.09.2019 06:00	19.09.2019 10:00	19.09.2019 06:10	True	2	Early
12974079	19.09.2019 07:15	19.09.2019 06:10	19.09.2019 07:24	False	2	x
14083299	09.11.2019 22:15	12.11.2019 07:40	11.11.2019 16:20	True	1	Early
14095669	09.11.2019 22:15	12.11.2019 07:40	11.11.2019 16:20	True	1	Early
577839859	09.11.2019 22:15	12.11.2019 07:30	11.11.2019 15:30	True	1	Early
590648829	31.10.2019 04:45	31.10.2019 15:59	04.11.2019 15:59	True	1	Same
696619829	31.10.2019 04:45	04.11.2019 15:59	31.10.2019 15:59	True	1	Same
758872829	29.10.2019 05:15	29.10.2019 07:30	29.10.2019 13:00	True	1	Same
769419829	31.10.2019 05:00	31.10.2019 13:00	31.10.2019 07:30	True	1	Same
777060829	29.10.2019 10:00	30.10.2019 11:00	29.10.2019 15:59	True	1	Same
786876829	29.10.2019 05:15	29.10.2019 13:00	29.10.2019 07:30	True	1	Same
796501759	04.10.2019 03:00	04.10.2019 07:30	07.10.2019 07:30	True	1	Same
803627829	31.10.2019 04:45	31.10.2019 15:59	04.11.2019 15:59	True	1	Same
809511829	29.10.2019 03:15	29.10.2019 19:00	29.10.2019 12:20	True	1	Same
827110759	04.10.2019 03:00	07.10.2019 07:30	04.10.2019 07:30	True	1	Same
841336829	29.10.2019 03:15	29.10.2019 07:00	29.10.2019 19:00	True	1	Same
848708829	31.10.2019 05:00	31.10.2019 07:30	31.10.2019 13:00	True	1	Same
859069849	07.11.2019 04:16	07.11.2019 05:30	07.11.2019 05:10	True	1	Same
861868859	09.11.2019 15:30	11.11.2019 12:20	11.11.2019 19:00	True	1	Late
869370859	09.11.2019 15:30	11.11.2019 12:20	11.11.2019 19:00	True	1	Late
947624829	01.11.2019 15:15	04.11.2019 09:50	01.11.2019 21:50	True	1	Same
977938829	31.10.2019 04:45	04.11.2019 15:59	31.10.2019 15:59	True	1	Same

 As can be seen, the formula seems to work perfectly for transports that occur several times.

There seems to be an error for transports that occur only once. Because "same" should not be possible for them.

Unfortunately I don't understand what is wrongly calculated, because "same" occurs only in a small percentage for transports that occur only once. Out of 1134 transports that occur once, "same" is given in about 0.6 % of cases.

I would be very grateful if you could help me in any way you can. I would also be happy to provide you with further information

I have filled out my request to the best of my knowledge. But you can reach me at any time.

Have a wonderful day!

Cheers!

qwertzuiop

[sturlaws · Accepted solution]

@qwertzuiop,

assuming you want to create a calculated column, use this:

EarlyLateSameOnTransport =
IF (
    'Table'[checkFirstTime];
    VAR currentTransportID =
        CALCULATE ( SELECTEDVALUE ( 'Table'[transportId (N)] ) )
    VAR min_date =
        CALCULATE (
            MIN ( 'Table'[created (S)] );
            FILTER ( ALL ( 'Table' ); 'Table'[transportId (N)] = currentTransportID )
        )
    VAR max_date =
        CALCULATE (
            MAX ( 'Table'[created (S)] );
            FILTER ( ALL ( 'Table' ); 'Table'[transportId (N)] = currentTransportID )
        )
    VAR a =
        CALCULATE (
            MAX ( 'Table'[etaAlt (S)] );
            FILTER (
                ALL ( 'Table' );
                'Table'[transportId (N)] = currentTransportID
                    && 'Table'[created (S)] = min_date
            )
        )
    VAR b =
        CALCULATE (
            MAX ( 'Table'[eta (S)] );
            FILTER (
                ALL ( 'Table' );
                'Table'[transportId (N)] = currentTransportID
                    && 'Table'[created (S)] = max_date
            )
        )
    RETURN
        SWITCH ( TRUE (); a > b; "Early"; a < b; "Late"; a = b; "Same" );
    "X"
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[qwertzuiop]

@sturlaws thank you so much for your work!

Wish you all the best.