calculate time

[Anonymous]

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 have seconds (568) instead. Any ideas how to solve this.

thnx

M

[v-yulgu-msft]

Hi @Anonymous,

Please try below formula. Please notice that in my sample data, there existing space between numeric and text.

Time in seconds =
VALUE ( LEFT ( Table4[Time], 2 ) ) * 60
    + VALUE ( LEFT ( RIGHT ( Table4[Time], 4 ), 2 ) )

Best regards,
Yuliana Gu

[fhill]

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

[Anonymous]

Hi @Anonymous,

Can you check if this code(measure) works:

Time = MINUTE(Table[TimeColumn1])*60+SECOND(Table[TimeColumn2])

Regards,

L.Meijdam

[Anonymous]

Hi,

thanks for your reply, tried the formula but Power BI explains that this expression cannot be used/found...

I think it is a format issue of this field as it has numbers and characters in it (9 min 28s).....

Any other ideas?

[Anonymous]

Hi @Anonymous,

Perhaps you could format it to a actual "time" value, or make a new column with actual time data. The problem now is that Power BI sees your values as (probably) text not as a number or time value.

Regards,

L.Meijdam

[Anonymous]

Hi, Hope this will help:

Avg. Visit Duration (in seconds)

2 min 37s

1 min 21s

0s

0s

1 min 14s

5s

45s

9 min 7s

5s

0s

31s

0s

36s

3s

0s

[v-yulgu-msft · Accepted solution]

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]

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