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 work...
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.

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

mohammedmahadik 

 

pllease share some sample data with the expected output . 

 

best regards

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.