Forum Discussion
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...
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
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
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
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 GuHi Yuliana, thanks for your suggestion which works fine!!!