Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Travel Distance

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

DrHematite

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2019
08:24 PM

I have some data in the following format:

Machine ID | Date | Latitude | Longitude

1 | 12/02/2019 | -29.37584 | 130.38429

2 | 12/02/2019 | -29.64453 | 131.49304

1 | 13/02/2019 | -29.37610 | 130.38419

2 | 13/02/2019 | -29.65132 | 131.41320

And I need to be able to measure the distance each machine (by machine ID) has travelled on a given day (An example, in the row for Machine ID 1, the distance on 12/02 would be the difference between its position on 12/02 and 13/02.)

I know the formula for the displacement:*Δ = acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*R *Where lat1, lat2, lon1 and lon2 are the respective latitudes and longitudes, and R is the radius of the Earth.

Problem is, I can't figure out how to refer to the next record by date, for a given machine ID.

Any suggestions?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

**Owen Auger**

**Did I answer your question? Mark my post as a solution!**

Connect on Twitter

Connect on LinkedIn

OwenAuger

Super User I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2019
08:57 PM

Here's an example of DAX code that could be used in a calculated column to find the next date for the current machine, and the corresponding Lat & Long, which can then be used to compute the distance (I've called the table **Machines**😞

Distance = VAR CurrentDate = Machines[Date] VAR NextDate = CALCULATETABLE ( FIRSTNONBLANK ( Machines[Date], 0 ), ALLEXCEPT ( Machines, Machines[Machine ID] ), Machines[Date] > CurrentDate ) VAR Lat2 = CALCULATE ( SELECTEDVALUE ( Machines[Latitude] ), ALLEXCEPT ( Machines, Machines[Machine ID] ), NextDate ) VAR Long2 = CALCULATE ( SELECTEDVALUE ( Machines[Longitude] ), ALLEXCEPT ( Machines, Machines[Machine ID] ), NextDate ) ...

You could also do something similar in Power Query if you preferred.

Connect on Twitter

Connect on LinkedIn

2 REPLIES 2

Highlighted

**Owen Auger**

**Did I answer your question? Mark my post as a solution!**

Connect on Twitter

Connect on LinkedIn

OwenAuger

Super User I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2019
08:57 PM

Here's an example of DAX code that could be used in a calculated column to find the next date for the current machine, and the corresponding Lat & Long, which can then be used to compute the distance (I've called the table **Machines**😞

Distance = VAR CurrentDate = Machines[Date] VAR NextDate = CALCULATETABLE ( FIRSTNONBLANK ( Machines[Date], 0 ), ALLEXCEPT ( Machines, Machines[Machine ID] ), Machines[Date] > CurrentDate ) VAR Lat2 = CALCULATE ( SELECTEDVALUE ( Machines[Latitude] ), ALLEXCEPT ( Machines, Machines[Machine ID] ), NextDate ) VAR Long2 = CALCULATE ( SELECTEDVALUE ( Machines[Longitude] ), ALLEXCEPT ( Machines, Machines[Machine ID] ), NextDate ) ...

You could also do something similar in Power Query if you preferred.

Connect on Twitter

Connect on LinkedIn

Highlighted
##

DrHematite

Frequent Visitor

Re: Travel Distance

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2019
09:12 PM

@OwenAuger

I think there's a problem somewhere in my formula (as I'm getting nonsense answers), but I've used your example to return the Lat2 and Long2 to new columns for testing purposes in the meantime, and that seems to have worked correctly.

Appreciate your help!

Top Solution Authors

User | Count |
---|---|

412 | |

313 | |

81 | |

58 | |

46 |

Top Kudoed Authors

User | Count |
---|---|

565 | |

502 | |

162 | |

141 | |

131 |