Summarize tabel on last date values

[icturion]

Hi,

I have a table with data (see table below).

From the table, I only want to use the records with the last time per value in the column "kenteken". I also want to be able to use the values ​​in the columns lon and lat.

This is what i got so far:

New table --> Laatste locatie = SUMMARIZE(AQZ_TRAVEL_MON,AQZ_TRAVEL_MON[Kenteken], "Laatst bekend", MAX(AQZ_TRAVEL_MON[Date time]))

result is

How do i now included the columns lon en lat with the correnspondig values?

[v-easonf-msft · Accepted solution]

Hi, @icturion 

You can add two calculated columns as below:

lon =
LOOKUPVALUE (
    AQZ_TRAVEL_MON[lon],
    AQZ_TRAVEL_MON[kenteken], 'Laatste locatie'[kenteken],
    AQZ_TRAVEL_MON[Date time], 'Laatste locatie'[Laatst bekend]
)

lat = 
LOOKUPVALUE (
    AQZ_TRAVEL_MON[lat],
    AQZ_TRAVEL_MON[kenteken], 'Laatste locatie'[kenteken],
    AQZ_TRAVEL_MON[Date time], 'Laatste locatie'[Laatst bekend]
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The other day I read an article about SUMMARIZE, and came away a bit shellshocked. 

All the secrets of SUMMARIZE - SQLBI

In a nutshell - you're likely better off using GROUPBY()

In any case you can add Lat and Lon to the groupings.