Forum Discussion

tiagotas's avatar
tiagotas
Icon for Helper I rankHelper I
9 years ago
Solved

Problems with hours greater than 24 hours

I have a worksheet that tells me the general amount of hours the machine stopped:


 

But I'm not getting a sum of the values ​​when the hours are over 24 hours on Power BI. It is showing an error!

 

With this error, I can not add the hours of the machine time indicator! How do I resolve this issue?

 

In the Power BI result, the 24:44 does not appear, or any other value above 24:00 hours.

Tips and Tricks
  • tiagotas's avatar
    tiagotas
    9 years ago

    I just used the change in value for Duration in Table Editing.
    That way he showed me the duration of the machine stops.

     

13 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    It's because times above 24:00 hours don't exist. In fact, these are durations.

    For more information on durations just search this forum.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi tiagotas,

    In your scenario, please create new columns using formulas below .

    Hour = LEFT(Table1[Column1],2)
    Minutes = RIGHT(Table1[Column1],2)
    Transfertoseconds = Table1[Hour]*60*60+Table1[Minutes]*60
    Time = FORMAT(Table1[Transfertoseconds]/86400,"Short Time")

    At last, change Time column to Time type under Modeling ribbon, you can also change the format of this Time column here.


    Thanks,
    Lydia Zhang

    • tiagotas's avatar
      tiagotas
      Icon for Helper I rankHelper I
      9 years ago

      I just used the change in value for Duration in Table Editing.
      That way he showed me the duration of the machine stops.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi tiagotas,

        It seems that you have resolved this issue. If so, you can accept helpful reply as answer, that way, other community members would easily find the answer when they get same issue.


        Thanks,
        Lydia Zhang

    • tiagotas's avatar
      tiagotas
      Icon for Helper I rankHelper I
      8 years ago

      I did as you taught me, but schedules above 24 hours do not have the correct value, which would be "24:44".

       

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Even after waiting a year, times >= 24:00 still don't exist.

        They won't exist next year, nor 10 years from now, nor 100 years from now.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    DAX Mode ( In this mode you can agregate for any dimension)

     

    QtSegundos = SUM([Seconds])

     

    Time=
VAR Horas = INT([QtSegundos]/3600)
VAR Minutos = INT(([QtSegundos] - (Horas * 3600))/60)
VAR Segundos = MOD([QtSegundos];60)
RETURN
Horas&":"&FORMAT(Minutos;"00")&":"&FORMAT(Segundos;"00")

     

    • Pmorg73's avatar
      Pmorg73
      Icon for Post Patron rankPost Patron
      6 years ago

      I was investigating this very problem. And having spent ages looking for a solution I found an easy one.

       

      In query editor. Select the column and split it by deliminator

      You get two columns. 1 is hours, 1 is mins

      New column = hours + (mins/60) = decimal number and works with values over 24 hours. 

      • PaulMac's avatar
        PaulMac
        Icon for Helper IV rankHelper IV
        5 years ago

        Great answer! Solutions to our problems should all be like this, be simple yet elegant 👍

  • JoshuaWolski's avatar
    JoshuaWolski
    Regular Visitor
    5 years ago

    This looks at the value, and formats Total hours in two different ways, if under 24 hours, and if over 24 hours. This might be what you are looking for. It shows totals over 24 hours. Help this helps!

     



    IF(VALUES('Table1[Column])<1,Format(Average('Table1[Column]), "hh:nn:ss"), QUOTIENT(VALUES(''Table1[Column]),0.0416666666666667) & ":" & Format(Mod(Values('Table1[Column]),1), "nn:ss"))