Forum Discussion

RalphO's avatar
RalphO
Icon for Helper I rankHelper I
7 years ago
Solved

Distance between start and endtime

Hey all,

 

I have the following data structure: 

 

IDTotalDistanceStartTimeEndTime
1100010:00 
11100  
11300  
11400 12:00
1140012:20 
2200014:00 
22200  
22300 14:30
2240015:00 
22400 15:10
35009:00 
3550  
3600 9:20
36009:25 
3630  
3650 9:50

 

Now, I want to calculate the distance travelled between each start and end time for each ID. How would I go about doing this? If possible in a measure!


Ralph

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    Hi RalphO

     

    Here is how you can do it with a calculated column.  I have attached a PBIX file

     

    Column = 
VAR StartTotal = 
    MAXX(
        FILTER(
            'Table1',
            Table1[ID] = EARLIER('Table1'[ID]) &&
            'Table1'[TotalDistance] < EARLIER('Table1'[TotalDistance]) &&
            NOT ISBLANK('Table1'[StartTime])
            
            ),[TotalDistance])
RETURN 
    IF(
        NOT ISBLANK('Table1'[EndTime]),
        'Table1'[TotalDistance] - StartTotal
        )

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    HI RalphO

     

    Can you please provide what your expected output would be for that sample set of data.  This will help clarify your requirement.

     

    Cheers,

     

    Phil

    • RalphO's avatar
      RalphO
      Icon for Helper I rankHelper I
      7 years ago

      hi Phil_Seamark

       

      I should look  something like this (A TripDistance value for the other rows should also be fine): 

       

      IDTotalDistanceStartTimeEndTimeTripDistance
      1100010:00  
      11100   
      11300   
      11400 12:00400
      1140012:20  
      2200014:00  
      22200   
      22300 14:30300
      2240015:00  
      22450 15:1050
      35009:00  
      3550   
      3600 9:20100
      36009:25  
      3630   
      3650 9:5050

       

      Basically  I want the total distance travelled in each trip, with trip defined as the time between starttime and endtime. 

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi RalphO

         

        Here is how you can do it with a calculated column.  I have attached a PBIX file

         

        Column = 
VAR StartTotal = 
    MAXX(
        FILTER(
            'Table1',
            Table1[ID] = EARLIER('Table1'[ID]) &&
            'Table1'[TotalDistance] < EARLIER('Table1'[TotalDistance]) &&
            NOT ISBLANK('Table1'[StartTime])
            
            ),[TotalDistance])
RETURN 
    IF(
        NOT ISBLANK('Table1'[EndTime]),
        'Table1'[TotalDistance] - StartTotal
        )