Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Lpetters
New Member

Best way to calculate distance between two addresses for thousands of addresses or zip codes.

Good Morning, 

 I'm a new user in the Power BI world and would like to try to figure out a way to select the most optimal ship from Distribution center based on the customers zip code and its proximity to the Distribution Center. 

 

For example, I have one Distrubution center in California and one in Maine.  I have 100k Orders, based on customer zip code and/or address. I would like to determine which Distribution Center makes the most sense to ship form to get the  the quickest time in transit. 

 

Any help would be greatly appreciated!

2 REPLIES 2
Kedar_Pande
Super User
Super User

Using the Haversine formula:

Distance = 
VAR CustomerLat = Customers[Latitude]
VAR CustomerLong = Customers[Longitude]
VAR CenterLat = DistributionCenters[Latitude]
VAR CenterLong = DistributionCenters[Longitude]
VAR Radius = 6371 -- Earth's radius in kilometers
VAR LatDiff = RADIANS(CenterLat - CustomerLat)
VAR LongDiff = RADIANS(CenterLong - CustomerLong)
VAR a = SIN(LatDiff / 2) * SIN(LatDiff / 2) + COS(RADIANS(CustomerLat)) * COS(RADIANS(CenterLat)) * SIN(LongDiff / 2) * SIN(LongDiff / 2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1 - a))
RETURN Radius * c

After calculating the distances, create a measure to determine which distribution center is closest to each customer.

OptimalDistributionCenter = 
IF(
[DistanceToCenter1] < [DistanceToCenter2],
"California Distribution Center",
"Maine Distribution Center"
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

thanks @Kedar_Pande  I will give this a try.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.