Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors