Velocity of Each User every time longitude & latitude is captured (Excel Example/Solution Provided)
I have GPS locations for many users arranged by datetime. I would like to pull this from from the DB in Power BI and create a new column for distance and another for velocity for each user between two longitude and latitude coordinates. B C D E F G H agent_user_id longitude latitude location_action verification_id verification_type created_at 3799 3.42061 6.42999 IN_TRANSIT 457265 ADDRESS 08/11/2021 18:01 47593 6.9825 4.81352 IN_TRANSIT 457435 ADDRESS 08/11/2021 18:01 3799 3.42067 6.43015 IN_TRANSIT 457265 ADDRESS 08/11/2021 18:03 40823 5.73639 5.54055 ONLINE 0 NONE 08/11/2021 18:03 42677 5.58848 6.2923 ONLINE 0 NONE 08/11/2021 18:03 47593 6.9825 4.81352 IN_TRANSIT 457435 ADDRESS 08/11/2021 18:03 26035 7.06534 4.84438 ONLINE 0 NONE 08/11/2021 18:04 26035 7.06534 4.84438 ONLINE 0 NONE 08/11/2021 18:04 In excel I did this by sorting the columns first by agent_user_id then by created_at. I then used the below formulas to calculate time between pings, distance between coordinates and velocity. For time and distance, the if statement does the calculation only if its the same agent_user_id else results in 0. Columns listed above begin with B. time_hr distance_km kmph =IF(B2=B1,(H2-H1)*24,0) =IF(B2=B1,ACOS(COS(RADIANS(90-D2)) * COS(RADIANS(90-D1)) + SIN(RADIANS(90-D2)) * SIN(RADIANS(90-D1)) * COS(RADIANS(C2-C1))) * 6371,0) =IFERROR(J2/I2,0)