Find lastest TIME of day in series of dates in DAX / M ?

[PbiConsult1]

Assume a series of dates in this format.  Client wants to determine the latest time of day (i.e. 7:00 AM in this data) several processes finish to find the specific date of the latest process finish times per week, month, quarter and year.

1/1/2001 6:00 AM

2/1/2002 7:00 AM

3/1/2003 4:45 AM

Open to any options. Thinking SQL might be easiest but does anyone know how to do this using just DAX / M.?

[Greg_Deckler]

Here's a better one:

Max Time = 
VAR tmpTable1 = VALUES(ProcessFinish[ProcessFinish])
VAR tmpTable2 = ADDCOLUMNS(tmpTable1,"Hour",TIMEVALUE([ProcessFinish]))
RETURN FORMAT(MAXX(tmpTable2,[Hour]),"Medium Time")

[PbiConsult1]

Greg:

Will that give the actual calendate for the latest time of day each process finishes?  Think I see where it finds the time component, just not the date. Thanks for being so quick to reply.

[Greg_Deckler]

Sorry, that was not clear from your original message. See if this one does the trick:

dateTime with Max Time = 
VAR tmpTable1 = VALUES(ProcessFinish[ProcessFinish])
VAR tmpTable2 = ADDCOLUMNS(tmpTable1,"Time",FORMAT(TIMEVALUE([ProcessFinish]),"Medium Time"))
VAR maxTime = MAXX(tmpTable2,[Time])
RETURN CALCULATE(MAXX(FILTER(tmpTable2,[Time]=maxTime),[ProcessFinish]))

[PbiConsult1]

Played with it but the error "end of input was reached" pops with a yellow triangle. Sounds like it's done parsing and isn't putting the results in the right place or somehow there is a table or column issue being treated as scalar perhaps?

[PbiConsult1]

Greg:

Thanks will let you know but looks good on first glance.

Really appreciate it!

[Greg_Deckler]

Maybe something like this:

Max Hour = 
VAR tmpTable1 = VALUES(ProcessFinish[ProcessFinish])
VAR tmpTable2 = ADDCOLUMNS(tmpTable1,"Hour",HOUR([ProcessFinish]))
RETURN MAXX(tmpTable2,[Hour])

[PbiConsult1]

 

Greg:

Runs!!! Hoo yah!!!

This is the output

Time is in 2 forms. 12am-12pm and Decimal. There were no visuals that could post time of day by hour on the Y axis so I had to convert to decimal time.  Those 22,23,21 all show up for each month which look remarkably similar to business day count per month.  A table needs to show the  exact date of the latest time of day finish d for each week, month,quarter, and year and singled out both for the table and for the visual.  No matter what visual is used or where in a table or matrix this is displayed that exact same column of 21,22,23 shows up the same for each different process finish.  Seems like it's close.  Thinking it's almost there but hoping you can help with that last stop. The idea is to be able to single out these values a little more so it would be something like this

ProcessName   Process finish   Latest Hour & Date Forweek   Latest Hour & Date ForMonth       Latest Hour & Date For Quarter   

.  Your earlier post is very helpful Greg.