Calculate time interval to next message

[qwertzuiop]

Hello dear PowerBI-Community

I hope you guys could help me with the following problem.

In the example (see table) there are several transports.

Each transport has an unique ID.

A transport can create 1 to n messages.

Actually my need is to measure the time interval to the next message on a transport.

For this reason i would like to create a calculated table in Power BI (see table - red column "timeGapToNextMessageOnTransportID")

transportID	messageCreated	timeGapToNextMessageOnTransportID
1	09.12.2019 10:15	7.25 h
1	09.12.2019 17:30	x
2	10.12.2019 11:45	x
3	12.12.2019 13:00	13.0 h
3	13.12.2019 02:00	2.5 h
3	13.12.2019 04:30	0.5 h
3	13.12.2019 05:00	x

I you have any questions, please ask.

I would be very thankful if one of you guys could help me.

Have a great time!

Cheers.

qwertzuiop

[MFelix]

Hi @qwertzuiop ,

Create the following measure:

Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
            'Table'[messageCreated];
            'Table'[transportID]
        );
        'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
            && 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
    )
VAR Current_Date =
    SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
    DATEDIFF ( Current_Date; MAXX ( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
    IF ( Time_difference = 0; "-"; FORMAT ( Time_difference; ""##0.0# h" h" ) )

I'm assuming you don't want to have this calculated on total time level.

check PBIX file attach.

[qwertzuiop]

Hello @MFelix 

Thank you so much for you support and your well structured text and attached file.

It makes it very easy to follow your thoughts.

Nevertheless I have some questions:

• How is your measure linked to the calculated column named "timeGapToNextMessageOnTransportID"?
  Because, if I focus on this column there is no stored formula (see figure 1)
• If I try to create directly a calulated column with your formula (see figure 2) all I get is the value "-" for every line - why is this?
• Is it possible to display the output as a decimal number instead of a string - so also without the letter "h" - excuse me

figure 1

figure 2

Thank you so much for your help.

Your contribution is very valuable.

Cheers!

qwertzuiop

[qwertzuiop]

I don't know why but figure 2 was missing.

sorry

figrue 2

[qwertzuiop]

Excuse me

Now I got it.

The calculated table is the one out of my example.

But then why is this error?

[MFelix]

Hi @qwertzuiop ,

My bad when I was testing out the information placed a MAXX when should be a MINX it's giving the difference to 5AM and not 2AM redo the measure to:

Measure = 
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
            'Table'[messageCreated];
            'Table'[transportID]
        );
        'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
            && 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
    )
VAR Current_Date =
    SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
    DATEDIFF ( Current_Date; MINX( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
    IF ( Time_difference = 0; "-"; FORMAT ( Time_difference; "##0.0# h" ) )

Corrected file attach.

[qwertzuiop]

Hello @MFelix 

Thank you so much for your quick answer and your great support.

The error is fixed.

But may I ask you some more things:

• How can I turn the output into a decimal number instead of a string like "7.25 h" -> What I would like is 7,25 - excuse my change
• If I try this formula with a measure in my masterdocument with over 30'000 diffrent TransportID's with over 50'000 created messages it is still loading and nothing happens...(see figure). Is there a way to create a calulated column with this information instead of a measure - I think with this way I could solve the problem.

Thank you so much.

You're a great member of the Community 🙂

Cheers!

qwertzuiop

[MFelix · Accepted solution]

Hi @qwertzuiop ,

Measure = 
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
            'Table'[messageCreated];
            'Table'[transportID]
        );
        'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
            && 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
    )
VAR Current_Date =
    SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
    DATEDIFF ( Current_Date; MINX( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
    Time_difference