Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

calculate time

Hi all,   am using a xls file as a source for a new report and one of the columns is an average of a duration of a visit. The only thing is that it is displayed like this: 9 min 28s and i want to h...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try this modified DAX formula to create a calculated column.

    Time in seconds =
IF (
    NOT ( ISERROR ( FIND ( "min", Table5[Avg. Visit Duration (in seconds)] ) ) ),
    VALUE ( LEFT ( Table5[Avg. Visit Duration (in seconds)], 2 ) ) * 60
        + VALUE ( LEFT ( RIGHT ( Table5[Avg. Visit Duration (in seconds)], 3 ), 2 ) ),
    VALUE (
        LEFT (
            Table5[Avg. Visit Duration (in seconds)],
            LEN ( Table5[Avg. Visit Duration (in seconds)] ) - 1
        )
    )
)

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please try this modified DAX formula to create a calculated column.

Time in seconds =
IF (
    NOT ( ISERROR ( FIND ( "min", Table5[Avg. Visit Duration (in seconds)] ) ) ),
    VALUE ( LEFT ( Table5[Avg. Visit Duration (in seconds)], 2 ) ) * 60
        + VALUE ( LEFT ( RIGHT ( Table5[Avg. Visit Duration (in seconds)], 3 ), 2 ) ),
    VALUE (
        LEFT (
            Table5[Avg. Visit Duration (in seconds)],
            LEN ( Table5[Avg. Visit Duration (in seconds)] ) - 1
        )
    )
)

 

Best regards,
Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Yuliana, thanks for your suggestion which works fine!!!