Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row.
Help when you know. Ask when you don't!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |