Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Processing Utilization Hours per Day | How to correctly visualize?

Hi all -

 

I'm currently vizualzing processing times per day for robots that are being ran. To find the processing time in HOURS, I created this calculated column:

 

Processing Time = IF(JobsDB[jobStart] = BLANK(),BLANK(),
IF(JobsDB[jobEnd] = BLANK(), DATEDIFF(JobsDB[jobStart],JobsDB[queryTime],MINUTE)/60,DATEDIFF(JobsDB[JobStart],JobsDB[jobEnd],MINUTE)/60))
 
So I have a start and end time for every job. If it's blank, it hasn't started yet (then blank), if JobEnd is blank, then it's still running, otherwise just get the date different in minutes/60 of start and end.
 
I then created a date table which is based on those start and end columns like so:
 
dimCalendar =

VAR BaseTable = CALENDAR(MIN(JobsDB[End Date]), MAX(JobsDB[End Date]))
Return
ADDCOLUMNS(
BaseTable,
"Year",YEAR([Date]),
"Month",FORMAT([Date], "MMMM"),
"Month Number", MONTH([Date]),
"Calendar Month",FORMAT([Date],"MMMM YY"),
"Month Year", FORMAT([Date],"YYYY MM"),
"Report Date",FORMAT([Date],"YYYY/MM/DD"))
 
 And I connected this date column to the end date column (for transaction metrics) - But now, I'm trying to accurately show how many hours processing per day. My issue is, there may be sometimes, when a process runs at 10 PM and end at 1 AM.. so that would mean that the visual would show that that Job/transaction ran for 3 hours on that next day. 
 
How do I solve this issue? I'd like to maybe modify the model or create a measure/calc column for processing time that ends at 11:59 before the next day so I can show these results correctly. If anyone has any idea as to how to go about this, I would really apperciate it.

Thank you!