Forum Discussion
Time difference between 2 dates not calculating when over 24hrs
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.
6 Replies
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!!
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.
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!!
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.
