How to get Latest Location and Total Engine hours

Question

Hey CommunitySO i Have a table which Consist of 15 Coulmns , Out of Which Few columns are Vin (Vehicle Identification Number),gps_timestamp(timestamp at which data recorded),engine hours (Current Engine hours at that time stamp),lat(lattitude of Vehicle ),Lon(Longitude of Vehicle)Sample data in below Tablevingps_timestampposition_latposition_lonENG_HOURSHAJSR16HCMG6799056/12/2021 15:3345.85492706-119.593032812.9HAJSR16HCMG6799056/12/2021 15:3445.85492706-119.593032812.9HAJSR16HCMG6799056/12/2021 15:3545.85493469-119.593032812.9HAJSR16HCMG6799056/12/2021 15:3645.85493469-119.593032812.9HAJSR16HCMG6799056/12/2021 15:3745.85493469-119.593032812.9HAJSR16HCMG6799056/12/2021 15:3845.85493851-119.593032813HAJSR16HCMG6799056/12/2021 15:3945.85493851-119.593032813HAJSR16HCMG6799056/12/2021 15:4045.85494232-119.593032813HAJSR16HCMG6799056/12/2021 15:4145.85494614-119.593032813HAJSR16HCMG6799056/12/2021 15:4245.85494614-119.593032813HAJSR16HCMG6799056/13/2021 16:3145.85493088-119.593025213HAJSR16HCMG6799056/13/2021 16:3245.85494614-119.593017613.1HAJSR16HCMG6799056/13/2021 16:3345.85494995-119.593017613.1HAJSR16HCMG6799056/13/2021 16:3445.85520172-119.592323313.1HAJSR16HCMG6799056/13/2021 16:3545.85327148-119.591667213.1HAJSR16HCMG6799056/13/2021 16:3645.85070801-119.591590913.1HAJSR16HCMG6799056/13/2021 16:3745.84867859-119.593879713.1HAJSR16HCMG6799056/13/2021 16:3845.84597397-119.593864413.2HAJSR16HCMG6799056/13/2021 16:3945.84357071-119.59177413.2HAJSR16HHNG68053510/21/2022 17:4840.89827347-98.384056091.3HAJSR16HHNG68053510/21/2022 17:4940.89728546-98.383163451.3HAJSR16HHNG68053510/21/2022 17:4940.8972702-98.383155821.3HAJSR16HHNG68053510/25/2022 13:0740.89726257-98.383239751.4HAJSR16HHNG68053510/25/2022 13:0740.89729309-98.383216861.4HAJSR16HHNG68053510/25/2022 13:0840.89728546-98.383216861.4HAJSR16HHNG68053510/25/2022 13:0940.89728165-98.383216861.4HAJSR16HHNG68053510/25/2022 13:1040.89727783-98.383216861.4HAJSR16HHNG68053510/25/2022 13:1140.89708328-98.382820131.4HAJSR16HHNG68053510/25/2022 13:1240.89632034-98.382606511.5HAJSR16HHNG68053510/25/2022 13:1340.89699554-98.382064821.5HAJSR16HHNG68053510/25/2022 13:1440.89718246-98.379936221.5HAJSR16HHNG68053510/25/2022 13:1540.89715958-98.380104061.5HAJSR16HHNG68053510/25/2022 13:1640.89715576-98.381546021.5HAJSR16HHNG68053510/25/2022 13:1740.89732742-98.383338931.5HAJSR16HHNG68053510/25/2022 13:1840.89733505-98.383308411.6HAJSR16HHNG68053510/25/2022 13:1940.8973999-98.385841371.6HAJSR16HHNG68053510/25/2022 13:2040.89766693-98.387786871.6HAJSR16HHNG68053510/25/2022 13:2140.89931488-98.388336181.6HAJSR16HHNG68053510/25/2022 13:2340.90034485-98.389892581.6HAJSR16HHNG68053510/25/2022 13:2440.90122986-98.389831541.6HAJSR16HHNG68053510/25/2022 13:2540.900177-98.389968871.7HAJSR16HHNG68053510/25/2022 13:2640.89981842-98.390632631.7Now , I want to Get Latest Location of Vehicle and Also Total Engine Hours . Engine Hours need to calculate from a New Calculated Column which has difference of each row for each vin and then we can sum by Vin to Get Total Engine Hours.Data is not sorted currently.&nbsp;Thanks for HelpRegardsRahul&nbsp;

anonymous · Answer

Hi&nbsp;rahul632soni&nbsp;,
1. Please try to create measure with below dax formula:
latest_position_lat =
VAR _vin =
    SELECTEDVALUE ( 'Table'[vin] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [vin] = _vin )
VAR _max =
    MAXX ( tmp, [gps_timestamp] )
VAR _val =
    CALCULATE ( MAX ( [position_lat] ), FILTER ( tmp, [gps_timestamp] = _max ) )
RETURN
    _val
latest_position_lon =
VAR _vin =
    SELECTEDVALUE ( 'Table'[vin] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [vin] = _vin )
VAR _max =
    MAXX ( tmp, [gps_timestamp] )
VAR _val =
    CALCULATE ( MAX ( [position_lon] ), FILTER ( tmp, [gps_timestamp] = _max ) )
RETURN
    _val
total engin hours =
VAR _vin =
    SELECTEDVALUE ( 'Table'[vin] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [vin] = _vin )
RETURN
    SUMX ( tmp, [ENG_HOURS] )

2. add a table visual with filed and measure

Please refer the attached .pbix file.
&nbsp;
Best regards,Community Support Team_Binbin YuIf this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

rahul632soni · Answer

Anonymous&nbsp;Hello Thakns For Solution ,However now thw things is, it has become a measure to calculate last Lat ans Lon so how would i use this to greate a map , What i wanted is to show the latest location of each machines .Currently when i ma trying to plot map its not happening .Thanks.&nbsp;

Forum Discussion

How to get Latest Location and Total Engine hours

2 Replies

Related content

Fabric Engineer

Data Engineering Role?

Topic: Feature Engineering & Data Leakage

Tool tip Location

Roster by Location

vin	gps_timestamp	position_lat	position_lon	ENG_HOURS
HAJSR16HCMG679905	6/12/2021 15:33	45.85492706	-119.5930328	12.9
HAJSR16HCMG679905	6/12/2021 15:34	45.85492706	-119.5930328	12.9
HAJSR16HCMG679905	6/12/2021 15:35	45.85493469	-119.5930328	12.9
HAJSR16HCMG679905	6/12/2021 15:36	45.85493469	-119.5930328	12.9
HAJSR16HCMG679905	6/12/2021 15:37	45.85493469	-119.5930328	12.9
HAJSR16HCMG679905	6/12/2021 15:38	45.85493851	-119.5930328	13
HAJSR16HCMG679905	6/12/2021 15:39	45.85493851	-119.5930328	13
HAJSR16HCMG679905	6/12/2021 15:40	45.85494232	-119.5930328	13
HAJSR16HCMG679905	6/12/2021 15:41	45.85494614	-119.5930328	13
HAJSR16HCMG679905	6/12/2021 15:42	45.85494614	-119.5930328	13
HAJSR16HCMG679905	6/13/2021 16:31	45.85493088	-119.5930252	13
HAJSR16HCMG679905	6/13/2021 16:32	45.85494614	-119.5930176	13.1
HAJSR16HCMG679905	6/13/2021 16:33	45.85494995	-119.5930176	13.1
HAJSR16HCMG679905	6/13/2021 16:34	45.85520172	-119.5923233	13.1
HAJSR16HCMG679905	6/13/2021 16:35	45.85327148	-119.5916672	13.1
HAJSR16HCMG679905	6/13/2021 16:36	45.85070801	-119.5915909	13.1
HAJSR16HCMG679905	6/13/2021 16:37	45.84867859	-119.5938797	13.1
HAJSR16HCMG679905	6/13/2021 16:38	45.84597397	-119.5938644	13.2
HAJSR16HCMG679905	6/13/2021 16:39	45.84357071	-119.591774	13.2
HAJSR16HHNG680535	10/21/2022 17:48	40.89827347	-98.38405609	1.3
HAJSR16HHNG680535	10/21/2022 17:49	40.89728546	-98.38316345	1.3
HAJSR16HHNG680535	10/21/2022 17:49	40.8972702	-98.38315582	1.3
HAJSR16HHNG680535	10/25/2022 13:07	40.89726257	-98.38323975	1.4
HAJSR16HHNG680535	10/25/2022 13:07	40.89729309	-98.38321686	1.4
HAJSR16HHNG680535	10/25/2022 13:08	40.89728546	-98.38321686	1.4
HAJSR16HHNG680535	10/25/2022 13:09	40.89728165	-98.38321686	1.4
HAJSR16HHNG680535	10/25/2022 13:10	40.89727783	-98.38321686	1.4
HAJSR16HHNG680535	10/25/2022 13:11	40.89708328	-98.38282013	1.4
HAJSR16HHNG680535	10/25/2022 13:12	40.89632034	-98.38260651	1.5
HAJSR16HHNG680535	10/25/2022 13:13	40.89699554	-98.38206482	1.5
HAJSR16HHNG680535	10/25/2022 13:14	40.89718246	-98.37993622	1.5
HAJSR16HHNG680535	10/25/2022 13:15	40.89715958	-98.38010406	1.5
HAJSR16HHNG680535	10/25/2022 13:16	40.89715576	-98.38154602	1.5
HAJSR16HHNG680535	10/25/2022 13:17	40.89732742	-98.38333893	1.5
HAJSR16HHNG680535	10/25/2022 13:18	40.89733505	-98.38330841	1.6
HAJSR16HHNG680535	10/25/2022 13:19	40.8973999	-98.38584137	1.6
HAJSR16HHNG680535	10/25/2022 13:20	40.89766693	-98.38778687	1.6
HAJSR16HHNG680535	10/25/2022 13:21	40.89931488	-98.38833618	1.6
HAJSR16HHNG680535	10/25/2022 13:23	40.90034485	-98.38989258	1.6
HAJSR16HHNG680535	10/25/2022 13:24	40.90122986	-98.38983154	1.6
HAJSR16HHNG680535	10/25/2022 13:25	40.900177	-98.38996887	1.7
HAJSR16HHNG680535	10/25/2022 13:26	40.89981842	-98.39063263	1.7