Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
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
v-kelly-msft
Community Support
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://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a90b6419-9bd3-439c-8100-1a5f1f4729a3&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

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

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
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://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a90b6419-9bd3-439c-8100-1a5f1f4729a3&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

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

 

Best Regards,
Kelly

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

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

 

Thanks in advance

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

 

Thanks in advance

 

 

 

 

 

amitchandak
Super User
Super User

Anonymous
Not applicable

Morning Amit,

 

Thanks for your reply.

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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