cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qwertzuiop Regular Visitor
Regular Visitor

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

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)checkFirstTimetransportIdOccurenceEarlyLateSameOnTransport
1293390916.09.2019 11:0017.09.2019 12:3016.09.2019 12:30True2Same
1293390917.09.2019 05:0016.09.2019 12:3017.09.2019 12:30False2x
1293528924.09.2019 03:3024.09.2019 08:3025.09.2019 08:30True2Late
1293528924.09.2019 20:0025.09.2019 08:3002.10.2019 08:30False2x
1297407919.09.2019 06:0019.09.2019 10:0019.09.2019 06:10True2Early
1297407919.09.2019 07:1519.09.2019 06:1019.09.2019 07:24False2x
1408329909.11.2019 22:1512.11.2019 07:4011.11.2019 16:20True1Early
1409566909.11.2019 22:1512.11.2019 07:4011.11.2019 16:20True1Early
57783985909.11.2019 22:1512.11.2019 07:3011.11.2019 15:30True1Early
59064882931.10.2019 04:4531.10.2019 15:5904.11.2019 15:59True1Same
69661982931.10.2019 04:4504.11.2019 15:5931.10.2019 15:59True1Same
75887282929.10.2019 05:1529.10.2019 07:3029.10.2019 13:00True1Same
76941982931.10.2019 05:0031.10.2019 13:0031.10.2019 07:30True1Same
77706082929.10.2019 10:0030.10.2019 11:0029.10.2019 15:59True1Same
78687682929.10.2019 05:1529.10.2019 13:0029.10.2019 07:30True1Same
79650175904.10.2019 03:0004.10.2019 07:3007.10.2019 07:30True1Same
80362782931.10.2019 04:4531.10.2019 15:5904.11.2019 15:59True1Same
80951182929.10.2019 03:1529.10.2019 19:0029.10.2019 12:20True1Same
82711075904.10.2019 03:0007.10.2019 07:3004.10.2019 07:30True1Same
84133682929.10.2019 03:1529.10.2019 07:0029.10.2019 19:00True1Same
84870882931.10.2019 05:0031.10.2019 07:3031.10.2019 13:00True1Same
85906984907.11.2019 04:1607.11.2019 05:3007.11.2019 05:10True1Same
86186885909.11.2019 15:3011.11.2019 12:2011.11.2019 19:00True1Late
86937085909.11.2019 15:3011.11.2019 12:2011.11.2019 19:00True1Late
94762482901.11.2019 15:1504.11.2019 09:5001.11.2019 21:50True1Same
97793882931.10.2019 04:4504.11.2019 15:5931.10.2019 15:59True1Same

 

 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.
 
adf.PNG
 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sturlaws New Contributor
New Contributor

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

@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.

View solution in original post

2 REPLIES 2
Highlighted
sturlaws New Contributor
New Contributor

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

@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.

View solution in original post

qwertzuiop Regular Visitor
Regular Visitor

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

@sturlaws thank you so much for your work!

Wish you all the best.

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)