cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculating (adding) distance between multiple locations using longitude and latitude

Hi all,

Any help with the following would be great as it is an unique situation. A quick intro into the problem,

1) I have teams who work at different locations each day, and there's a latitude and longitiude for each address and a post code

2) On the PBI dashboard the end user wants to select any team/s and date/s to show the total average distance. For example Mr ABC did 3 jobs today; (1st Town to 2nd Town + 2nd Town to Last Town)/3

3) I have attempted many solutions;

-  Create two tables such that there's a lat, long, lat 1 and long 1 for measures

-  Used a measure below, however nothing works

4) This dataset is linked to the main datasey by an unique identifier DATE&NAME

Any help would be super appreciated, thanks in advance.

```Kilometers =
var Lat1 = MIN('From City'[lat])
var Lng1 = MIN('From City'[lng])

var Lat2 = MIN('To Cities'[lat])
var Lng2 = MIN('To Cities'[lng])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 +
COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A)))
return fina```

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

You could use Latitude and Longitude to calculate the distance between two locations with following formula:

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371

Then sum up the distances by each person and divide 3 to get the result you need.

ps: 6371 is Earth radius in km.

You can achieve it via either Power Query or DAX method.

Or you can use Google Map API .

You could also check the reference below:

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

Best Regards,
Kelly

6 REPLIES 6
Community Support

Hi @Anonymous ,

You could use Latitude and Longitude to calculate the distance between two locations with following formula:

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371

Then sum up the distances by each person and divide 3 to get the result you need.

ps: 6371 is Earth radius in km.

You can achieve it via either Power Query or DAX method.

Or you can use Google Map API .

You could also check the reference below:

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

Best Regards,
Kelly

Anonymous
Not applicable

Hi,

I'm unsure how to apply the above soluiton to my situation as follows:

1) I have a columns of; team names, long', lat' dates, unique identifier, address

2) Each team can do 3-4 jobs a day each with it's own long, lat

3) I need to show the total and average distance for team/s on any date or date ranges

Anonymous
Not applicable

Hi,

Can anyone help with the above?

Thanks

Anonymous
Not applicable

Hi,

I'm unsure how to apply the above soluiton to my situation as follows:

1) I have a columns of; team names, long', lat' dates, unique identifier, address

2) Each team can do 3-4 jobs a day each with it's own long, lat

3) I need to show the total and average distance for team/s on any date or date ranges

Super User
Anonymous
Not applicable

Morning Amit,

The formula in my post does not give the right answer. I will look at your links now and update you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors