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

[Anonymous]

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

[v-kelly-msft · Accepted solution]

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]

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]

Hi, 

Can anyone help with the above?

Thanks

[Anonymous]

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]

@Anonymous , is your formula not giving the correct result?

refer if needed

http://www.girlswithpowertools.com/2014/05/distance/

https://dhexcel1.wordpress.com/2015/08/16/measuring-distance-in-power-bi-desktop/

[Anonymous]

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.