Forum Discussion
Distance between start and endtime
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
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
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
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.
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 )
