Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.?
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")
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]))
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!
Maybe something like this:
Max Hour = VAR tmpTable1 = VALUES(ProcessFinish[ProcessFinish]) VAR tmpTable2 = ADDCOLUMNS(tmpTable1,"Hour",HOUR([ProcessFinish])) RETURN MAXX(tmpTable2,[Hour])
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
9 | |
6 | |
3 | |
3 | |
2 |