Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Difference in time between records in the same column

Hi all,

 

I had a question about a formula I wanted to create. I created a calculated column that shows the average time a object was at a certain point (TimeAtPoint) what I want to know is the time between two of those records. For example:

 

Through slicing I want to know what the time is between for example A and B which would give 00:10:00 in this example. Between A and C it would give 00:30:00 (this variable would be called TravelTime)

(TimeAtPoint is in H:mm:ss)

 

the next step is to calculate the distance between those 2 points, which could be achieved by TravelTime * speed. Although I have no idea how to achieve this yet.

 

Thanks in Advance !

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Perhaps use a measure that calculates the difference between the MIN and MAX of the column?

  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    Anonymous,

     

    Through slicing I want to know what the time is between for example A and B which would give 00:10:00 in this example.

    So you will select two items in your slicer? If that is the case, you can use the measure below.
    Difference = MAX(Table1[TimePoint])-MIN(Table1[TimePoint])

    Regards,

    Charlie Liao

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Perhaps use a measure that calculates the difference between the MIN and MAX of the column?

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    Anonymous,

     

    Through slicing I want to know what the time is between for example A and B which would give 00:10:00 in this example.

    So you will select two items in your slicer? If that is the case, you can use the measure below.
    Difference = MAX(Table1[TimePoint])-MIN(Table1[TimePoint])

    Regards,

    Charlie Liao

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi all,

       

      First of all thanks for taking time to help me.

       

      And yes this worked the same for me as in the example v-caliao-msft recreated. The next step in my formula would be to calculate a distance by "TimeDifference" * "speed". Although I am not sure how to approach this problem since I don't know how to make the multiply understand that I want "TimeDifference" multiplied by the speed the object had between point A and B or A and C for example. I was wondering if any of you had an idea on how to approach this problem.

       

      Regards,

       

      L. Meijdam