Forum Discussion

heidibb's avatar
heidibb
Helper IV
10 years ago
Solved

Duration Troubles

Hello,

I am working on a project around runs, the duration for each run in hh:mm:ss and the pace (duration/total miles). My data is being captured in a google sheet, where I have the column defined as duration and it is being entered as 0:25:00 for example for the total running time. 

When I pull the data in, it initially looks right and shows exactly how it shows within the google sheet, but when I apply and look at it in the data window, it's converted over to a decimal. I understand that this is showing as a portion of a day (where 25 minutes is about .01736 of a day), but I would like to keep the data so it shows as hh:mm:ss where I can then total up the total running time and the average pace per person, etc.

I have searched through the message board and I'm not seeing anything similar to this.

I should also say that I'm very new to Power BI and am using this project as my first to get familiar with the tool.

Thanks in advance!

Need Help
  • heidibb's avatar
    heidibb
    10 years ago

    Vvelarde YES, this worked almost perfectly!! I had to make a couple tweeks, changing your semi-colons to commas and had to fix the minute & seconds sections to account for leading zeros when we have single digits (see bolded section). If you see anything off with this, let me know, but I can't thank you enough!!

     

     

    DurationRUNNER =
    VAR DurationRUNNER =
    SUM ( RTB_Runs[Duration] )
    RETURN
    IF (
    ROUNDDOWN ( DurationRUNNER, 0 ) > 1,
    ROUNDDOWN ( DurationRUNNER, 0 ) * 24
    + HOUR ( DurationRUNNER )
    & ":"
    & (if(len(minute(DurationRUNNER)) > 1,minute(DurationRUNNER), "0" & minute(DurationRUNNER))
    & ":"
    & (if(len(second(DurationRUNNER)) > 1,second(DurationRUNNER), "0" & second(DurationRUNNER))
    )),
    FORMAT ( DurationRUNNER, "hh:mm:ss" )
    )

     

7 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    10 years ago

    Hi heidibb,

     

    In your scenario, you can change data type of the field as time, see:

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

    • heidibb's avatar
      heidibb
      Helper IV
      10 years ago

      Thank you v-qiuyu-msft! That helped with the format in my data, but it won't allow me to sum up the amount of time. It only allows me to count the number of rows per runner.

       

      So for example, if Beth had the follow runs and durations in June, I want to sum up total Duration where the final result is 13 hours, 20 minutes, 23 seconds (as hh:mm:ss it would be 13:20:23).

       

      Any thoughts?

       

      DateRunsMilesDurationPace
      5/31/2016130:26:000:08:40
      6/1/2016150:42:000:08:24
      6/3/2016150:42:000:08:24
      6/4/2016191:17:000:08:33
      6/5/2016150:42:000:08:24
      6/7/2016140:34:000:08:30
      6/8/2016150:44:000:08:48
      6/10/2016140:37:000:09:15
      6/11/2016170:59:000:08:26
      6/12/2016161:10:000:11:40
      6/13/2016150:43:000:08:36
      6/15/2016150:42:000:08:24
      6/17/2016140:34:000:08:30
      6/18/2016181:09:000:08:38
      6/19/2016160:56:000:09:20
      6/20/2016150:43:000:08:36
      6/21/2016150:40:430:08:09
      • Vvelarde's avatar
        Vvelarde
        Community Champion
        10 years ago

        hi heidibb

         

        Create a measure:

         

        Total-Duration = FORMAT(SUm(RUNS[Duration]);"HH:MM:SS")

         

        is : SS (with no space)