How to get Latest Location and Total Engine hours

[rahul632soni]

Hey Community

SO 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 Table

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

Now , 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.

Thanks for Help

Regards

Rahul

[v-binbinyu-msft]

Hi @rahul632soni ,

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.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rahul632soni]

@v-binbinyu-msft 

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.