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?

       

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

        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.