cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Memorable Member

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``````

5 REPLIES 5
Anonymous
Not applicable

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:

'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(

Avg Completion Time 2022 = CALCULATE(

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

Anonymous
Not applicable

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:

Memorable Member

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.

Resident Rockstar

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Memorable Member

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``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors
Users online (959)