Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PbiConsult1
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
Greg_Deckler
Super User
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")


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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?

Greg:

 

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

 

Really appreciate it!

 

 

Greg_Deckler
Super User
Super User

Maybe something like this:

 

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

 

Sort6.png 

 

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.

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.