Calculate time in hours and minutes between 2 times and two days - not working

[Anonymous]

Hello,

I have this table, the column completion time usually shows a time the next day (see data table, so if 18.02 the completion time at 03:20 means its 3:20AM 19.02), the start is always at 18 in the evening same day as column date. 

When I use this formula, I see wrong result...for example when the start time is 6PM and end time is 13:14 the next day, the duration is 17h14m instead I see 7.23 h...

formula I used is:

DiffTimeofLoad = DATEDIFF('Daily Issues'[Initial Time],'Daily Issues'[Completion time],MINUTE)/60

[Anonymous]

Hello,

I fugured how to calculate for exceptional days when the load was in the same day, first I create a load same date column:

LoadSameDay =

IF('Daily Issues'[DiffTimeofLoad]>25,"Y","N")

Then LoadTime calculated by:

LoadTime =

IF('Daily Issues'[LoadSameDay]="N", 'Daily Issues'[DiffTimeofLoad],DATEDIFF(

'Daily Issues'[Initial Time],'Daily Issues'[Completion time],MINUTE)/60

)

Now my issue is I try to calculate Average Load Time - I need to measures one for 2021 and one for 2022 as I want to use them in the 2 line trendline. But I get exactly the same result...why?

What I want i to build a trendline month by month similar to this one:

Avg Completion Time 2021 = CALCULATE(

AVERAGE('Daily Issues'[LoadTime]),'Date Table'[Year]=MAX('Date Table'[Year])-1)

Avg Completion Time 2022 = CALCULATE(

AVERAGE('Daily Issues'[LoadTime]),'Date Table'[Year]=MAX('Date Table'[Year]))

My table is linked to Date Table but I also have Year in my original parent table:

[Anonymous]

Dear vojtech,

It looks great to me. I just have 1 question on how to handle an exception when the completion time is not in the next day but in the current day, here below th calculation is wrong:

[vojtechsima]

Hi, @Anonymous ,
that's kinda complicated, with data structure like you have, you have to manually evaluate like, if the end of job is between 18-23:59, calculte result for today, othewrise for next day, but that's hard coded andnot  that flexible, but it will work if you know that the job won't take longer than 24 hours.

[ChrisMendoza]

@Anonymous - I attempted as:

Diff Time of Load =
VAR __initialDateTime =
    DATEVALUE ( TableName[Date] ) + TIMEVALUE ( TableName[Initial Time] )
VAR __completionDateTime =
    ( DATEVALUE ( __initialDateTime ) + 1 )
        + TIMEVALUE ( TableName[Completion Time] )
VAR Result = __completionDateTime - __initialDateTime
RETURN
    Result

with a format change as: 13:30 (hh:nn)

 

[vojtechsima · Accepted solution]

Hi,  @Anonymous 
Actually, I think the duration is 19,23 hours in your case.
Here's the solution: 

TimeDiff = 
var currentDay = 'Table'[Date] + 'Table'[Start]
var NextDay__ = ('Table'[Date]+1) + 'Table'[End]
var datedifCalc = DATEDIFF(currentDay, NextDay__, MINUTE) / 60
 return datedifCalc