Distance between start and endtime

[RalphO]

Hey all,

I have the following data structure: 

ID	TotalDistance	StartTime	EndTime
1	1000	10:00
1	1100
1	1300
1	1400		12:00
1	1400	12:20
2	2000	14:00
2	2200
2	2300		14:30
2	2400	15:00
2	2400		15:10
3	500	9:00
3	550
3	600		9:20
3	600	9:25
3	630
3	650		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

[Phil_Seamark]

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]

hi @Phil_Seamark

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

ID	TotalDistance	StartTime	EndTime	TripDistance
1	1000	10:00
1	1100
1	1300
1	1400		12:00	400
1	1400	12:20
2	2000	14:00
2	2200
2	2300		14:30	300
2	2400	15:00
2	2450		15:10	50
3	500	9:00
3	550
3	600		9:20	100
3	600	9:25
3	630
3	650		9:50	50

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

[Phil_Seamark · Accepted solution]

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
        )

[RalphO]

@Phil_Seamark That works, thank you!

For bonus points: do you think it would also be possible to do this with a measure instead of a calculated column?

[Phil_Seamark]

Hi @RalphO

Yes this would be possible, however a calculated measure needs to know what fields you are using in an axis to help ensure you get the right values.

Would you be using a Table/Matrix visual with the measure?

[RalphO]

@Phil_Seamark I will probably be using a line graph visual for it, with timestamps as the x-axis (one stamp for every 2 seconds, not included in the sample data). Would a measure work for this, or solely for an actual matrix/table?