Forum Discussion

nhmpp's avatar
nhmpp
Helper I
3 years ago

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=
MAXX (
    TOPN (
        1,
        FILTER (
            PredictEvents,
                PredictEvents[Event_End] > MAX(LocationRevenues[starts_at_in_time_zone])
                && PredictEvents[Event_Start] <= MAX(LocationRevenues[starts_at_in_time_zone])
        ),
        PredictEvents[Loc_Rank],DESC
    ),
    PredictEvents[Event_Name]
)

 

 

Km from Event for Table 2=

MAXX( TOPN (
        1,
        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])
        ),
        PredictEvents[Loc_Rank],ASC
    ) , [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. 
Need Help

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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