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

fhill's avatar
fhill
Resident Rockstar
8 years ago

Please copy and paste some sample data (or link to a small excel sample) so we can give it a go!  My thoughts are to do a Text Trim of the field Before " min" (convert to number) * 60 + Text Trim After "min " (exclude s).

FOrrest