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...
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])

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.