Forum Discussion

qwertzuiop's avatar
qwertzuiop
Icon for Advocate III rankAdvocate III
6 years ago
Solved

Calculate time interval to next message

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")

 

transportIDmessageCreatedtimeGapToNextMessageOnTransportID
109.12.2019 10:157.25 h
109.12.2019 17:30x
210.12.2019 11:45x
312.12.2019 13:0013.0 h
313.12.2019 02:002.5 h
313.12.2019 04:300.5 h
313.12.2019 05:00x

 

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

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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

7 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      qwertzuiop
      Icon for Advocate III rankAdvocate III
      6 years ago

      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's avatar
        qwertzuiop
        Icon for Advocate III rankAdvocate III
        6 years ago

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

        sorry

         

        figrue 2