cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justyna_kmiecik
Regular Visitor

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

 

justyna_kmiecik_1-1653060710142.png

 

 

1 ACCEPTED SOLUTION
vojtechsima
Super User
Super User

Hi,  @justyna_kmiecik 
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
 

vojtechsima_1-1653067232381.png

 

View solution in original post

5 REPLIES 5
justyna_kmiecik
Regular Visitor

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:
justyna_kmiecik_1-1653318503855.png

 

 
 
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:
 
justyna_kmiecik_0-1653318443348.png

 

 
 
 
 

 

justyna_kmiecik
Regular Visitor

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:

 

justyna_kmiecik_0-1653309802662.png

 

 

 

Hi, @justyna_kmiecik ,
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
Super User
Super User

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

 image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



vojtechsima
Super User
Super User

Hi,  @justyna_kmiecik 
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
 

vojtechsima_1-1653067232381.png

 

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors