Problems with hours greater than 24 hours

[tiagotas]

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.

[JoshuaWolski]

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

[Anonymous]

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]

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]

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

[Anonymous]

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]

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

[MarcelBeug]

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.

[tiagotas]

Oh really? I do not believe!

Maybe you can help me from a different Mandeira? Or have the solution to my problem.
I need to show the sum of the hours worked for each department. I have seen companies that pay more than 24 hours to the employee.

[MarcelBeug]

Already a year ago, I explained those values are durations, not times.

[MarcelBeug]

An alternative would be to use a "Total Hours" field and a text field that diplays the "Time" as you would like to see it:

let
    Source = #table(type table[Duration = duration],List.Zip({List.Durations(#duration(0,1,0,0),10,#duration(0,3,45,0))})),
    AddedHours = Table.AddColumn(Source, "Total Hours", each Duration.TotalHours([Duration]), type number),
    AddedTime = Table.AddColumn(AddedHours, """Time""", each Text.From(Number.RoundDown([Total Hours]))&":"&Text.PadStart(Text.From(Duration.Minutes([Duration])),2,"0"), type text)
in
    AddedTime

[tiagotas · Accepted solution]

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

[Anonymous]

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

[MarcelBeug]

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.