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

thnx

M

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    Can you check if this code(measure) works:

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

     

    Regards,

    L.Meijdam

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident 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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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