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 ...
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" )
    )

     

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)

 

 

 

  • heidibb's avatar
    heidibb
    Helper IV
    10 years ago

    Thank you Vvelarde!! this is almost there! Final question, the column totals are calculating a bit weird. Everything else looks great, but not sure how to address the column totals

     

    • heidibb's avatar
      heidibb
      Helper IV
      10 years ago

      I realize the grand totals for both months are off by 48 hours each, so it seems once it passes increments of 24 hours, it's dropping off. is there a way to just continue to total the hours indefinitly? This is the aggregation as it shows in excel. trying to replicate in Power BI.

       

          
       56Grand Total
      Anna0:56:004:06:025:02:02
      Beth0:26:0012:54:4313:20:43
      Brian0:29:008:07:208:36:20
      Christian 0:33:550:33:55
      Erin0:45:005:15:006:00:00
      Heidi 3:32:033:32:03
      Jeremy0:26:003:29:293:55:29
      Jillian0:32:097:18:207:50:29
      Josh0:25:506:55:007:20:50
      Justin0:25:551:05:311:31:26
      Michelle 3:16:303:16:30
      Grand Total4:25:5456:33:5360:59:47
      • Vvelarde's avatar
        Vvelarde
        Community Champion
        10 years ago

         

        Hi heidibb

         

        Try this measure (hope it works)

         

        Duration-RUNNER =
        VAR DurationRUNNER =
            SUM ( Runners[Duration] )
        RETURN
            IF (
                ROUNDDOWN ( DurationRUNNER; 0 ) > 1;
                ROUNDDOWN ( DurationRUNNER; 0 ) * 24
                    + HOUR ( DurationRUNNER )
                    & ":"
                    & (
                        MINUTE ( DurationRUNNER ) & ":"
                            & SECOND ( DurationRUNNER )
                    );
                FORMAT ( DurationRUNNER; "hh:mm:ss" )
            )