I have a calculated measure named "Miles". This takes the longitude of a home address and measures the distance to the stores located near it. However, I want to be able to calculate whether the store that the delivery occured from is actually the closest store. I have the store number that the order was shipped from. For example, in table A I have customer lat/long and the store that it was shipped from. In table B, I have all stores lat/longs. I then created a Miles formula see below:
Miles =
var Lat1 = MIN('Ex Delivery Task Data'[Postal Codes.LAT])
var Lng1 = MIN('Ex Delivery Task Data'[Postal Codes.LNG])
var Lat2 = MIN('Distance From Stores'[Latitude])
var Lng2 = MIN('Distance From Stores'[Longitude])
---- 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 = 7919.2 * ASIN((SQRT(A)))
return final
But this only will work when I click on a customer value (which is what I want). However, I want to create another measure that calculates whether the order was shipped from the closest store, but I do not want to have to select a specific customer order. I want to see the general population.
