Get a messure for the last report of a day

[Anonymous]

Good morning,

Im trying to get the value of a field, in this case "Total vehicle hours (SPN 246)" , of the last message of a day.

I mean for example as you can see:

In this case you can see that the data contains 5 rows, each row correspond to a message at a specific moment. The first one is for a vehicle with ID 802000232. The message is created with de following date: year 2020, month 3, day 22 and hour 6 and minutos 17 and seconds 45. So each lines is the status of the vehicle at that time. 

What i want is to know the value of the field "Total vehicle hours (SPN 246)"  for the last record of a day and vehicle.

In this case for vehicle 802000232 the last total vehicle hours for day 22 at month 3 will be 650.

The goal with this is to be able to define a messure tha is going to respresent the total vehicles hour per day (and vehicle) to be able to compare de productivity of the different vehicles

Thank you

[danextian]

Hi @Anonymous ,

Try this:

Create a calculated column for the transaction timestamp. Something like:

Date Time =
DATE ( Data[Year], Data[Day], Data[Day] )
    + TIME ( Data[Hour], Data[Minute], Data[Second] )

Create another calculated column to determine whether a timestamp is the max for the day and return the total vehicle hours.

Last Vehicle Hours =
VAR __MAX_TIMESTAMP =
    CALCULATE (
        MAX ( Data[Date Time] ),
        ALLEXCEPT ( Data, Data[Year], Data[Month], Data[Day] )
    )
RETURN
    IF ( Data[Date Time] = __MAX_TIMESTAMP, Data[Vehicle Hours] )

You can use this calculated column in a measure.

[V-lianl-msft]

Hi @Anonymous ,

You could try to create the following measure and apply it to the visual level filter.

Measure =
IF (
    MAX ( 'table'[Total vehicle hours] )
        = CALCULATE (
            MAX ( 'table'[Total vehicle hours] ),
            ALLEXCEPT ( 'table', 'table'[VIN], 'table'[Year], 'table'[Month], 'table'[Day] )
        ),
    1
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for answering!!

I tried the way you said but the messure is returning a '1' instead of the value of the 'Total vehicle hours' field for the last record of the day.

[V-lianl-msft]

Hi @Anonymous ,

Please apply it to the visual level filter.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for answering!

Im not sure if i understand what you mean by "Please apply it to the visual level filter".

In the visualitation, filters i have choosen the new messure created.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can refer to the sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bheepatel]

Hi @Anonymous 

I would try the following:

1. In that table, create two new columns and call them FullDate and FullTime. You can use the built-in DAX functions DATE() and TIME() and both those functions will ask for Year,Month,Day & Hours,Minutes,Seconds respectively.

2. You can then create the following measure:

Last Total Hours =

VAR vDate = MAX(Table[FullDate])

VAR vTime = CALCULATE(MAX(Table[FullTime]), Table[FullDate] = vDate)

VAR vHours = CALCULATE(MAX(Table[Hours]), Table[FullDate] = vDate, Table[FullTime] = vTime)

RETURN vHours

Hope that helps!