Forum Discussion
Distance between start 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 )
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
)
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?
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?
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?