Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Distance from Location based on overlapping times

I have data about lots of differnet locations, and then separate info on events going on in the area. I have a formula set up for distance between the event and the different locations, and the table looks like this, where you filter by one location at a time.





Next, I have a table that shows mutliple locations at a time, and differnet revenue that they earned per day. Those calculations are not important. When there is an event from the events table that overalps with that certain date, the name of it appears in the "PredictHq Event" column, filtered by highest "local rank" which is how popular an event is. There are usually multiple events per day, so this is the reason for that. What I need in the final column is the KM from that event that gets pulled, to the specific location of that row. Right now, it is calulcating as shown, even though the first table is calculating correctly. I think it may be an issue of not working right with the event name. 



Here are my formulas right now:


PredictHq Event 1=
    TOPN (
        FILTER (
                PredictEvents[Event_End] > MAX(LocationRevenues[starts_at_in_time_zone])
                && PredictEvents[Event_Start] <= MAX(LocationRevenues[starts_at_in_time_zone])



Km from Event for Table 2=

        FILTER (
            PredictEvents, PredictEvents[Event_Name] = LocationRevenues[PredictHqEvent1]
                && PredictEvents[Event_End - Copy] > MAX(LocationRevenues[starts_at_in_time_zone - Copy])
                && PredictEvents[Event_Start - Copy] <= MAX(LocationRevenues[starts_at_in_time_zone - Copy])
    ) , [KmFromLocation]
KmFromLocation = 
var Lat1 = MIN(Locations[latitude])

var Lon1 = MIN(Locations[longitude])

var Lat2 = MIN(PredictEvents[Lat])

var Lon2 = MIN(PredictEvents[Lon])

var P = DIVIDE( PI(), 180 )

var A = 0.5 - COS((Lat2-Lat1) * p)/2 +

COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lon2- Lon1) * p))/2

var final = 12742 * ASIN((SQRT(A)))

return final
I appreciate any help! I am not able to share the powerbi file. 
Community Support
Community Support

Hi @nhmpp ,

According to your discription "the KM from that event that gets pulled, to the specific location of that row", what "specific location" means?


Best Regards,
Community Support Team _ kalyj

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors