Forum Discussion

Smudgers9's avatar
Smudgers9
Frequent Visitor
2 years ago

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 -...
Need Help
Kishore_KVN's avatar
Kishore_KVN
Solution Sage
2 years ago

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's avatar
Smudgers9
Frequent Visitor
2 years ago

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's avatar
    Kishore_KVN
    Solution Sage
    2 years ago

    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's avatar
      Smudgers9
      Frequent Visitor
      2 years ago

      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.