03-07-2020 11:43 AM - last edited 05-13-2020 00:06 AM
Uses the Haversine formula to compute the distance between the latitudes and longitudes of two points.
c = VAR __FromCity = SELECTEDVALUE('From City'[City]) VAR __ToCity = SELECTEDVALUE('To City'[City]) VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity) VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity) VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity) VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity) VAR __distanceLong = RADIANS(__ToLong - __FromLong) VAR __distanceLat = RADIANS(__ToLat - __FromLat) VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2 VAR __y = SQRT(__a) VAR __x = SQRT(1 - __a) VAR __atan2 = SWITCH( TRUE(), __x > 0, ATAN(__y/__x), __x < 0 && __y >= 0, ATAN(__y/__x) + PI(), __x < 0 && __y < 0, ATAN(__y/__x) - PI(), __x = 0 && __y > 0, PI()/2, __x = 0 && __y < 0, PI()/2 * (0-1), BLANK() ) VAR __c = 2 * __atan2 RETURN __c
c is the main measure and then you need to multiple this by the radius of the earth in either km, miles, etc.
Note, this does not account for the elliptical shape of the Earth so don't use it for anything besides estimation as you could be off a few miles over long distances.
OK, I downloaded the UK Postal Code latitudes and longitudes from here:
You guys sure have a lot of postal codes, apparently about 1.7M of them and since they are unique as well as the latitudes and longitudes, well, it makes for a fairly sizeable file. Anyway, I went ahead and implemented a few columns and such to get the distances.
However, they only allow a maximum upload file size of 50MB and the file is nearly twice that. So I am uploading to my personal OneDrive and will share out a file from there. Hang tight.