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

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

        Excuse me

        Now I got it.

        The calculated table is the one out of my example.

        But then why is this error?