0

Can anybody please tell me why I am way off?  I am working on getting distance between two points on Power BI /Excel. I did my research online and  I found a very common formula used by everyone.  So  tested it out, my distance is way off. Please see below,  enter image description here

The distance between Logan and Draper is 111 miles, but I am getting 3750 miles.  I hard coded the formula to test it out. I know this is not 100% accurate, but I am completely off. 

 Orginal formula =ACOS(SIN(Lat1)*SIN(Lat2) +COS(Lat1)*COS(Lat2)*COS(Lon2-Lon1)) *3958.756

My hard coded one = COS(SIN(40.51)*SIN(41.73) +COS(40.51)*COS(41.73)*COS(-111.78-(-111.87))) *3958.756

Thank you so much

Suneth
  • 199
  • 1
  • 3
  • 13

2 Answers2

1

In your searches you may have seen this thread here, but just in case I'm offering it.

Right off the bat, I do not see any use of Pi in your formula which makes me wonder if you've converted your latitudes to radians. When I did a quick, dirty, back of the napkin calc and converted the lats to be expressed in rads then the answer came out much closer (error probably due to my horrific rounding of PI/180.

This site may also be of use.

I hope I've at least pointed you in the right direction to get it going.

Courtney Foster
  • 383
  • 2
  • 10
  • Can you please share how did you calculate it? I have note converted to radians. I would appreciate if you share it. I look at your site too. It just the same formula tho. I want to run thin in excel, ideally power bi. Please share how you calculated. Thank you – Suneth Sep 30 '20 at 23:52
1

Following up from the previous answer, you need to convert your latitude and longitude to radians.

Following the formula from this website I get the same results in DAX of 134.9 km. The site has a calculator, from which I get the same result in DAX.

Distance = 

var _lat1 = RADIANS(40.524670)
var _lon1 = RADIANS(-111.863823)
var _lat2 = RADIANS(41.7379)
var _lon2 = RADIANS(-111.8308)

var R = 6371

var _dif1 = (_lat2 - _lat1)
var _dif2 = (_lon2 - _lon1)

var _a = (sin(_dif1/2)^2) + cos(_lat1) * cos(_lat2) * (sin(_dif2/2) ^ 2)

var _c  =  2*ASIN(SQRT(_a))

return _c * R
Angelo Canepa
  • 1,701
  • 2
  • 13
  • 21