cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

7 REPLIES 7
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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?

Frequent Visitor

Greg:

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

Really appreciate it!

Super User

Maybe something like this:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.