Forum Discussion

RalphO's avatar
RalphO
Helper I
7 years ago
Solved

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 ...
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
        )

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
7 years ago

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's avatar
RalphO
Helper I
7 years ago

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?