Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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.
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.
Proud to be a Super User!
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.
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.
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.
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.
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.
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:
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |