How to calculate hours between to DateTimes & show hours per day on x axis?

[Anonymous]

Hi all -

bit of a tricky situation here - I have a start time and end time column, and a lot of those transactions happen around midnight.

I need to show total hours per day, but because sometimes the end date time is the next day, the total hours per day is all messed up. Doesn't matter if I use start time or end time for X axis - numbers are still wrong.

What can I do to fix this? Is there a way for me to differentiate between the two days when that happens? Appreciate any input.

[v-yalanwu-msft]

Hi, @Anonymous ;

If the interval involves two days, you can use if(), and then use the next day (0:00:00) as the end time, and the next day (0:00:00) as the end time  as the starting time, so you can create two measures, one to calculate the daily interval of startTime and one to calculate the daily interval of EndTime, and then summarize them as follows:

1.create a datediff base on starttime.

Processing Time (start) = var _maxstart=MAX([StartTime])
return  
IF (
    _maxstart = BLANK (),BLANK (),
    IF (
        MAX ( [EndTime] ) = BLANK (),
        DATEDIFF ( _maxstart , MAX ( [queryTime]), MINUTE ) / 60,
        IF(
            DATEDIFF(_maxstart,MAX([EndTime]),DAY)>=1,DATEDIFF(_maxstart,DATE(YEAR(_maxstart),MONTH(_maxstart),DAY(_maxstart)+1),MINUTE)/60,
        DATEDIFF (_maxstart, MAX ( [EndTime] ), MINUTE ) / 60
)))

2.create a datediff base on endtime.

Processing Time (end) =
VAR _end =
    MAX ( [EndTime] )
RETURN
    IF (
        MAX ( [StartTime] ) = BLANK (), BLANK (),
        IF ( _end = BLANK (),0,
            IF (
                DATEDIFF ( MAX ( [StartTime] ), _end, DAY ) >= 1,
                DATEDIFF ( DATE ( YEAR ( _end ), MONTH ( _end ), DAY ( _end ) ), _end, MINUTE ) / 60,0)))

The final output is shown below:

3.create a table.

Table = SUMMARIZE('JobsDB',[ID],[StartTime], [EndTime],[queryTime],"Srart",FORMAT([StartTime],"yyyy-mm-dd"),"end",FORMAT([EndTime],"yyyy-mm-dd"),"process-s",[Processing Time (start)],"process-e",[Processing Time (end)])

4.create a sum measure.

total =
CALCULATE (
    SUM ( [process-s] ),
    FILTER ( ALL ( 'Table' ), [Srart] = MAX ( [end] ) )
)
    + CALCULATE ( SUM ( [process-e] ), ALLEXCEPT ( 'Table', 'Table'[end] ) )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

What @AllisonKennedy proposed is the way to do this with DAX.  It's not an easy calculation.  If you can transform your data further, you can simplify things.  For an example, create a blank query, open the advanced editor and replace the M code there with this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00Dc01TcyMDJUsLAyNFBw9EUVNDS0MgKLxuogNJghaQiAaTCHCJpYGcPVO6GZZYCiAWqKEdRaZA1QKXOo3RBBC4igMdSQ2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Line = _t, StartTime = _t, EndTime = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "StartTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"StartDate", "StartTime"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "EndTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"EndDate", "EndTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"StartDate", type date}, {"EndDate", type date}, {"StartTime", type time}, {"EndTime", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate])+1, #duration(1,0,0,0))),
    #"Expanded DateRange" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DateRange",{{"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NewStartTime", each if [StartDate] = [Date] then [StartTime] else #time(0,0,0), type time),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewEndTime", each if [EndDate] = [Date] then [EndTime] else #time(23,59,59), type time),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Line", "Date", "NewStartTime", "NewEndTime"})
in
    #"Removed Other Columns"

It shows how to convert a table like this

into a table like this

where there is a row for every date a line is in operation.  It works even if the operation spans 3+ days.  You can then relate the one date column to your Date table.  A model like this should simplify other calculations too (e.g., days in operation).  This is a fairly common use case, and a good topic for a video.  Thanks for the idea.

Also, please see this article for an easy way to calculate your total duration from a table like this (without adding a duration column subtracting the two time columns).

Calculate and Format Durations in DAX – Hoosier BI

Pat

[Anonymous]

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!

[AllisonKennedy · Accepted solution]

@Anonymous  I first off don't recommend using DAX for your Date table, use Power Query : https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Next, don't relate the Date table to your job table if you want the 3 hours split over multiple days. Instead, you'll need a much more complicated DATEDIFF code using the Date table: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

You'll need a start and end of day DateTime column in your Date table to make things easier, so I'd add 12:00am and 11:59pm to the Date column as two new columns in your date table: 

= Table.AddColumn(#"Inserted Week of Month", "Suffix", each Text.From([Date], "en-NZ") & " 12:00 am", DateTime.Type)

The Processing Time can be calculated with a DAX Measure: 

Processing Time =

VAR _Result =

SUMX(JobsDB,

VAR _JobStart = JobsDB[jobStart]

VAR _JobEnd = IF(ISBLANK(JobsDB[jobEnd]), JobsDB[queryTime], JobsDB[jobEnd])

VAR _DimDate = FILTER(DimDate,

(DimDate[Start of Day] <= _JobStart && _JobStart <= DimDate[End of Day] )

||( DimDate[Start of Day] <=_JobEnd && _JobEnd <= DimDate[End of Day] )

)

VAR _DayStart = MINX(_DimDate, DimDate[Start of Day])

VAR _DayEnd = MAXX(_DimDate, DimDate[End of Day] )

VAR _Start = MAX(_JobStart, _DayStart)

VAR _End = MIN(_JobEnd, _DayEnd)

RETURN

DATEDIFF(_Start, _End, MINUTE)/60

)

RETURN

_Result

Eventually you get this result (see attached file below signature): 

[bhargavikalluri]

Hi , 

I have a different scenario similar to this, the transaction might run more than two days. When I followed this approach, I am able to acheive this. How to display between dates with 24 hours time?Result

[AllisonKennedy]

@Anonymous  I believe I replied to you in a previous post - UPDATE : I have merged your previous post with this one and also see you've cross posted in the DAX forum too: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Processing-Utilization-Hours-per-Day-How-to-correctly-visualize/m-p/2138057 It's better to post only once so we can all see when it's answered and get inspiration from the other replies and also have all the info needed. I'll keep the DAX one posted, but will add a note that it's cross posted. When you resolve one, please be sure to mark ALL posts as solved. 

Thanks!

did you get my sample file? I think it does what you're asking for: 

If not, please provide screenshots and indicate what's wrong/needs changed. 

If it does, and you can't replicate, again provide screenshots and details of what you don't understand/need more help with.

Cheers!

File is attached below signature.