Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tiagotas
Helper I
Helper I

Problems with hours greater than 24 hours

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

Hora.png

 

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!

horas power bi.png

 

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.

1 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.

 

paradas.png

View solution in original post

13 REPLIES 13
JoshuaWolski
Regular Visitor

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!

JoshuaWolski_0-1607348191208.png

 



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
Not applicable

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

 

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. 

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

v-yuezhe-msft
Employee
Employee

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.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Screenshot_1.png

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.

Specializing in Power Query Formula Language (M)

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.

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

Specializing in Power Query Formula Language (M)

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

 

Times beyond 24 hours.png

Specializing in Power Query Formula Language (M)

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

 

paradas.png

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.