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

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.

QuasemS's avatar
QuasemS
Frequent Visitor
10 years ago

I would get a raw number for that column. Do I format it using this:

 

Hours = INT([Duration in Seconds]/3600)
Minutes = INT(MOD([Duration in Seconds],3600)/60)
Seconds = MOD(MOD([Duration in Seconds],3600),60)
H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))
M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))
S = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    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
      8 years ago

      This is exactly what I'm looking for, but it doesn't work in Direct Query mode, do you have any alternatives for that?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Importing the data?

    • SWI-Inc's avatar
      SWI-Inc
      Regular Visitor
      4 years ago

      My data doesnt contain any days, when i use this power BI won't move the ifo to the table because of the zero days.