Forum Discussion

QuasemS's avatar
QuasemS
Frequent Visitor
10 years ago
Solved

Calculating day/time difference

I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type to date/time, it gives me an error which makes sense because it's looking for an actualy date, whereas I am trying to calculate the difference between two dates.

 

Anybody know a good way to do this?

 

Thanks

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    QuasemS

     

    You can also add a custom column in Query Editor.

    =Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])

     

     

    And then in DAX

    Column = 
IF (
    LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 )
        = "",
    0,
    LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 )
)
    & " DAY "
    & RIGHT ( Table1[Duration], 8 )

     

    Or Duration.ToRecord and expand?

    Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Also can use a measure, i feel little faster loading time with this 


    TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)

     
    and then change the format to time hh:mm:ss
    Cheers
    • TrentAssist's avatar
      TrentAssist
      Frequent Visitor
      6 years ago

      Thanks, this idea helped me on a similar issue i was batttling with. 

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago
    QuasemS wrote:

    I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type to date/time, it gives me an error which makes sense because it's looking for an actualy date, whereas I am trying to calculate the difference between two dates.

     

    Anybody know a good way to do this?

     

    Thanks

    QuasemS

     

    What is the format of those two column? The data type conversion error indicates the format can't predicated correctly. Please post some sample of those column, if they're in the same format, it can be easy to re-format and convert to date.

    • QuasemS's avatar
      QuasemS
      Frequent Visitor
      10 years ago

      Here's a snapshot of the two columns. I'm trying to find the difference between the second and the first one:

       

      So the result wouldn't be a date, but in days/minutes/seconds format.

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        10 years ago

        In your Query Editor window, add a custom column with a formula of:

         

        [AlarmSentUTC] - [AlarmClearedUTC]

        This will result in a Duration field that you can then use the blog article I posted earlier to format into the format you want.