Finding the latest time for a row for a single day

[jacklamlan]

Hello,

I have data that looks something like this:

Date	Job Name	End Time
1/1/2022	Job A	4:45pm
1/1/2022	Job B	5:35pm
1/1/2022	Job C	3:30pm
1/2/2022	Job D	4:30pm
1/2/2022	Job E	6:00pm
1/2/2022	Job F	8:00pm

I am looking for a dax expression that can calculate the latest end time for the given day. I would like the data to look like this:

Date	Job Name	End Time	Latest Time of the Day
1/1/2022	Job A	4:45pm	5:35pm
1/1/2022	Job B	5:35pm	5:35pm
1/1/2022	Job C	3:30pm	5:35pm
1/2/2022	Job D	4:30pm	8:00pm
1/2/2022	Job E	6:00pm	8:00pm
1/2/2022	Job F	8:00pm	8:00pm

Does anyone know a dax expression that could do this? Thanks.

[v-rongtiep-msft · Accepted solution]

Hi @jacklamlan ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    MAX ( 'Table'[End time] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
            && 'Table'[Job] = SELECTEDVALUE ( 'Table'[Job] )
    )
)

Or a column.

Column =
CALCULATE (
    MAX ( 'Table'[End time] ),
    FILTER (
        ( 'Table' ),
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Job] = EARLIER ( 'Table'[Job] )
    )
)

Best Regards
Community Support Team _ Polly

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