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

     

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

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        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