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

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

PbiConsult1 — Thu, 12 Apr 2018 00:55:58 GMT

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.?

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

Greg_Deckler — Mon, 09 Apr 2018 13:12:44 GMT

Maybe something like this:

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

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

Greg_Deckler — Mon, 09 Apr 2018 13:14:39 GMT

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")

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

PbiConsult1 — Mon, 09 Apr 2018 13:54:45 GMT

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.

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

Greg_Deckler — Mon, 09 Apr 2018 14:06:53 GMT

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]))

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

PbiConsult1 — Mon, 09 Apr 2018 15:05:05 GMT

Greg:

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

Really appreciate it!

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

PbiConsult1 — Tue, 10 Apr 2018 01:13:28 GMT

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?

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

PbiConsult1 — Thu, 12 Apr 2018 00:50:10 GMT

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.