Obtain the last value in a column per date

[Rumbles]

Hi all,

I have a table that, for example, tracks location throughout the day.

I would like to have a column indicating the last location on each particular date. How would I go about that?

As a bonus, it would be great to have an additional column that would show the last location say before 2am on each day.

eg. on 21/04/2018, the last location of the day was Kintbury, although it was beyond midnight, so with the above logic, it would have counted 'Savile Club'.

Does that make sense?

Rumbles

[v-yulgu-msft · Accepted solution]

Hi @Rumbles,

Try this:

Last Location =
CALCULATE (
    LASTNONBLANK ( Location[Location], 1 ),
    FILTER (
        ALLEXCEPT ( Location, Location[Date] ),
        Location[Time]
            = CALCULATE ( MAX ( Location[Time] ), ALLEXCEPT ( Location, Location[Date] ) )
    )
)

Best regards,

Yuliana Gu