Help me optimize this formula

[mohammedmahadik]

I have a table with date and time-wise location of employees.
I want to find the distance traveled by employee from previous location for all the rows on a given date.

The following formula works but is too long and I was wondering if there is a better way to do it.

CALC_Distance From Previous Location =

var employeeID = 'Visit Report'[Employee ERP ID]

var currentDate = 'Visit Report'[CALC_Date]

var currentTime = 'Visit Report'[CALC_Time]

var prevTimeStamp = CALCULATE(

    MAX('Visit Report'[CALC_Time]),

        FILTER(

    'Visit Report',

    'Visit Report'[Employee ERP ID] = employeeID &&

    'Visit Report'[CALC_Date] = currentDate &&

    'Visit Report'[CALC_Time] < currentTime))

var Lat1 = 'Visit Report'[Visit Lattitude]

var Lon1 = 'Visit Report'[Visit Longitude]

var Lat2 = CALCULATE(

    MAX('Visit Report'[Visit Lattitude]),

    FILTER(

        'Visit Report',

        'Visit Report'[Employee ERP ID] = employeeID &&

        'Visit Report'[CALC_Date] = currentDate &&

        'Visit Report'[CALC_Time] = prevTimeStamp))

var Lon2 = CALCULATE(

    MAX('Visit Report'[Visit Longitude]),

    FILTER(

        'Visit Report',

        'Visit Report'[Employee ERP ID] = employeeID &&

        'Visit Report'[CALC_Date] = currentDate &&

        'Visit Report'[CALC_Time] = prevTimeStamp))

var Pi = DIVIDE(PI(),180)

var Factor = 0.5 - COS((Lat2-Lat1) * Pi)/2 + COS(Lat1 * Pi) * COS(lat2 * Pi) * (1-COS((Lon2- Lon1) * Pi))/2

var Dist = 12742 * ASIN(SQRT(Factor))

RETURN

    IF(OR(Lat2=0,Lon2=0),0,Dist*1000)

[v-tianyich-msft · Accepted solution]

Hi @mohammedmahadik ,

You can refer to the following expression:

Pre Latitude = 
var _t = ADDCOLUMNS(ALLSELECTED('Table'),"Rank",RANKX(FILTER(ALL('Table'),[Employee ID]=EARLIER([Employee ID])&&[Date]=EARLIER([Date])),[Time],,ASC,Dense))
var _t2 = ADDCOLUMNS(_t,"Pre2",MAXX(FILTER(_t,[Employee ID]=EARLIER([Employee ID])&&[Rank]=EARLIER([Rank])-1),[Visit Latitude]))
RETURN 
SUMX(FILTER(_t2,[Employee ID] = MAX('Table'[Employee ID]) && [Date] = MAX('Table'[Date]) && [Time] = MAX('Table'[Time])),[Pre2])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel29195]

@mohammedmahadik 

pllease share some sample data with the expected output . 

best regards

[mohammedmahadik]

Hi Daniel,

I want to add the columns "Previous Latitude" and "Previous Longitude" as shown in the image.

Basically, the latitude and longitude for a particular employee on a given date on the previously available time.