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

     

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" )
          )
      • heidibb's avatar
        heidibb
        Helper IV
        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" )
        )