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