Forum Discussion

mohammedmahadik's avatar
mohammedmahadik
Frequent Visitor
2 years ago
Solved

Help me optimize this formula

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)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

    • mohammedmahadik's avatar
      mohammedmahadik
      Frequent Visitor
      2 years ago

      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.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.