Time difference between 2 dates not calculating when over 24hrs

[Smudgers9]

I am trying to display the difference in time taken between 2 columns.  

My data source is Excel.  Dates must be in UK format.

Column 1 - Date Logged (format Custom dd/mm/yyyy hh:mm)

Column 2 - Date Resolved (format Custom dd/mm/yyyy hh:mm)

Column 3 - Resolution Time (format Custom [h]:mm:ss

In Excel this is working correctly.

When I move this ito Power BI

This works fine until the total time goes over 24 hours and then it starts from zero again. 

For example (one that works) - 

Date Logged - 09/11/2023 16:03:00

Date Resolved - 10/11/2023 15:57:00

Resolution Time - 23:54:00

Example that fails

Date Logged - 10/04/2024 10:26:00

Date Resolved - 12/04/2024 15:45:00

Resolution Time - 5:19:00 (should be 53:19:00)

I can't work out how to show the correct difference between the columns in hours and minutes when spanning over more that a 24hr period.

Apprecite your help.

[Kishore_KVN]

Hello @Smudgers9 , 

I was not able to figure out best was because of time constraint but I have a alternative which will give you result in text format. Not sure if this help you or not but let me share with you. You may have to create a calculated column using below DAX:

Time Difference Col = 
Var Days_diff = DATEDIFF('Table'[Date Logged],'Table'[Date Resolved],DAY)
Var Hours_diff = DATEDIFF('Table'[Date Logged],'Table'[Date Resolved],HOUR)
Var Total_Hours = Days_diff*24
Return
IF(Hours_diff<24,
HOUR('Table'[Date Resolved]-'Table'[Date Logged])&":"&
MINUTE('Table'[Date Resolved]-'Table'[Date Logged])&":"&
SECOND('Table'[Date Resolved]-'Table'[Date Logged]),
HOUR('Table'[Date Resolved]-'Table'[Date Logged])+Total_Hours&":"&
MINUTE('Table'[Date Resolved]-'Table'[Date Logged])&":"&
SECOND('Table'[Date Resolved]-'Table'[Date Logged]))

Then your output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Smudgers9]

Thanks @Kishore_KVN this is very useful, however there is a piece of information I didn't include as I assumed it wouldn't be needed but once I have the resolution time calculated I then need to work out the average of all resolution times, but this can't be done with a Text column.  

[Kishore_KVN]

Hello @Smudgers9 

In that case represent your column using below DAX. Even though its text create a measure for the average duration calculation as well. 
Firstly create a column like this:

Time Format = 
Var Days_diff = DATEDIFF('Table'[Date Logged],'Table'[Date Resolved],DAY)
Var Hours_diff = DATEDIFF('Table'[Date Logged],'Table'[Date Resolved],HOUR)
Var Total_Hours = Days_diff*24
Return
IF(Hours_diff<24, "0 hours + "&
FORMAT(Time(HOUR('Table'[Date Resolved]-'Table'[Date Logged]),
MINUTE('Table'[Date Resolved]-'Table'[Date Logged]),
SECOND('Table'[Date Resolved]-'Table'[Date Logged])),"HH:MM:SS"),
Total_Hours & " hours + "&
FORMAT(Time(HOUR('Table'[Date Resolved]-'Table'[Date Logged]),
MINUTE('Table'[Date Resolved]-'Table'[Date Logged]),
SECOND('Table'[Date Resolved]-'Table'[Date Logged])),"HH:MM:SS"))

Output looks as below:

Then your measure looks as below:

Average Duration = 
FORMAT(AVERAGEX('Table','Table'[Date Resolved]-'Table'[Date Logged]),"HH:MM:SS")

Output looks as below:

Because of the time constrains I have not validated so please validate solution. 

Note: In Power BI, the time data type is designed to handle times of the day, which means it cannot natively handle intervals that exceed 24 hours without rolling over. The built-in time data type will interpret '53:19:00' as '5:19:00 AM' of the next day because it is 53 hours and 19 minutes past a certain start point

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Smudgers9]

Thanks again @Kishore_KVN .

There are still some oddities happening.

The correct amount of time is circled on the left - Time to resolve

The Date Logged and Date Resolved are the 2 columns on the right

The middle cirlce is the text column using your DAX and it's odd because you can see it has correctly converted some results, but then the bottom one is widely off.  This is before I even try to average the results.

[lbendlin]

Remember that datetimes are numbers too. The Integer part is the number of days since Dec 30 1899, and the fraction is the time part.  Simply subtract one date from the other, multiply by 24 to get the number of hours and then add the minutes and seconds via FORMAT.

[Smudgers9]

Hi @lbendlin I'm not sure I follow.  I have done this but the results are a little strange as in the output should give me 53:19:00 but the number converted is 07:36:00.  Using Data Type - Time for the column.

ResolutionColumn = ('Table'[Date Resolved]-'Table'[Date Logged])*24