how to calculate period of stay at particular location

[Tejaswini_Dahat]

Hi all, I am trying to find the time taken by a device to complete a trip from base location to destination & return to base location.
My expected answer is column "Time taken"

// Base location = LOC1
// Time taken should be the time taken by the deviceID to return back to LOC1

Thanks in advance

[kentyler]

I did these steps in excel

first remove all the rows showing arrival at intermediate destinations

device id	location	timestamp	Time Difference	Time Taken
1	1	12/1/2019	0.00	0
1	1	12/6/2019	5.00	5
2	1	12/3/2019	-3.00	0
2	1	12/4/2019	1.00	1

then calculate the difference between the time for each row and the time for the row above it (since every trip is now reduced to 2 rows)

then take only the differences above 0, these are the time taken

this operation is straightforward in excel, complicated in DAX

once you have shaped the data, the reporting in Power BI will be greatly simplified.

if the data is in SQL you could use something like the LAG() function to get access to a different row

Overview of SQL Server LAG() function

SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row.

[Tejaswini_Dahat]

Thanks for your solution.
But I can't delete the rows that are now base location. I need to calculate the time for every location as well.