Converting decimal hours to friendly format (D:H:M) - Duration, not time.

[Anonymous]

Hello, hope you're all well.

Firstly, apologies if I'm posting in the incorrect forum here... I'm pretty new to posting in them.

I have a column within a table called "elapsedhrs", which essentially details an elapsed time/duration for a process. The values in the column are currently of a "Decimal" format, and are reflections of hours.

For example: -

• 0.0727777777
• 4.5165023660
• 65.123049596

The below is what I would like the above values converting to, if possible, or anything even remotely similar would be amazing: -

Elapsedhrs	ElapsedTime
0.0727777777	0 days : 0 hours : 4 minutes : 22 Seconds
4.5165023660	0 days : 4 hours : 30 minutes : 59 Seconds
65.123049596	2 days : 17 hours : 7 minutes : 23 Seconds

The labels such as days / hours etc. are not necessesary and are purely there for illustration purposes.

... And to make things even more difficult, there's also a requirement to obtain averages from this conversion, so the final values would have to be formatted in such a way that this would be compatible and work.

I've been trying... and failing for several hours now, and seem to have exhausted my own skillset and researching abilities, so would greatly appreciate any help at all.

I thought I managed to get somewhere close at one point, but soon realised the "Time" format wasn't suitable due to its 24 hour limitations.

Thanks in advance. Really appreciate any guidance at all.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download this PBIX with the below code.

OK so what you do is keep the Elapsed Hours as a decimal and then do the conversion to a string showing d.h.m.s in Measures.

For Elapsed Time with this

_Elapsed = 

VAR days = INT(SUM('Table'[ElapsedHrs])/24)
VAR hrs = INT(SUM('Table'[ElapsedHrs]) - days*24)
VAR mins = INT((SUM('Table'[ElapsedHrs])- days*24 - hrs)*60)
VAR secs = ROUND( MOD((SUM('Table'[ElapsedHrs]) * 60 - INT(SUM('Table'[ElapsedHrs])*60))*60,60),0)
RETURN
days & " d " & hrs & " h " & mins & " m " & secs & " s"

and for Average Elapsed Time with this

_Avg Elapsed = 

VAR days = INT(AVERAGE('Table'[ElapsedHrs])/24)
VAR hrs = INT(AVERAGE('Table'[ElapsedHrs]) - days*24)
VAR mins = INT((AVERAGE('Table'[ElapsedHrs])- days*24 - hrs)*60)
VAR secs = ROUND( MOD((AVERAGE('Table'[ElapsedHrs]) * 60 - INT(AVERAGE('Table'[ElapsedHrs])*60))*60,60),0)
RETURN
days & " d " & hrs & " h " & mins & " m " & secs & " s"

This responds to the selected filters

Regards

Phil

[ykhebde]

Hi @PhilipTreacy 

This has helped me too... Thanks

But I need it to be summed up in hours like in Excel format '[hh] : mm'  

In my data, I have a list of Employees who work for 9.5 Hrs a Day - which is 09:30 Hrs.

So if I have 20 employees - my total for that day is 9.5 x 20 = 190 Hours. which on Excel shows as 190:00 for that day and a 5-day week it becomes 950 Hours = 950:00 in Excel...

how can I produce this in Power BI to show me similar data as below:

[Anonymous]

Hi Phil,

Works a dream! This is incredible stuff, thank you so much for your time & effort on this, I can't tell you how much I appreciate it. I've been tearing my hair out over this.

Yet again, your help has been invaluable to me.  Thank you.

[PhilipTreacy]

No worries @Anonymous  🙂

[Vera_33]

@Anonymous 

This is DAX calcultated column:

Column = 
VAR days = INT([Column1]/24)
VAR hr =INT([Column1]-days*24)
VAR mins = INT(([Column1]-days*24-hr)*60)
VAR secs = ROUND( MOD(([Column1]*60-INT([Column1]*60))*60,60),0)
RETURN
days &" days: "& hr & " hours: " & mins &" minutes: "&secs&" seconds"

[Anonymous]

Thank you very much for your response, this has certainly got my data looking more presentable and friendly, particularly for the more static values.

I'll take this away and start looking into how I can make use of this whilst calculating averages.

Thanks again, I really appreciate your guidance & help.

[PhilipTreacy]

Hi @Anonymous 

Download this sample PBIX file with code

This Power Query code does the job, converting the decimals to durations

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzMDcyBwELpVidaCUTPVNDM1MDI2MzMzDfzFTP0MjYwMQSyI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Decimal Durations" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Decimal Durations", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each ([Decimal Durations] / 24)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each Number.Mod([Days],1)*24),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Minutes", each Number.Mod([Hours],1) * 60),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Seconds", each Number.Round(Number.Mod([Minutes],1) * 60)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Duration", each #duration(Number.RoundDown([Days]), Number.RoundDown([Hours]), Number.RoundDown([Minutes]), [Seconds])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Days", "Hours", "Minutes", "Seconds"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Duration", type duration}})
in
    #"Changed Type1"

If you want the "hours","mins" etc added that is quite easy to do by converting everything to a string.

I'm not sure how you intend to get the average duration but you can compute it like this for the whole column

= List.Average(Table[Duration])

If you provide examples of what you want I'll see what I can do to help.

In the file linked above I've created a query that calculates the Average and Total durations just so you can see how it can be done.

Regards

Phil

[Anonymous]

Thanks Phil, I really appreciate this thorough response.

However, I am having a bit of difficulty getting the values in the Duration field to display correctly in a visual. By default, it presents the values as decimals still, unless I change the format to TIME HH:MM:SS, which then removes the days value - Any ideas?

Just a bit more background for the average calculation requirement: - The report is essentially looking at tickets logged into an IT Helpdesk system, and the primary goal at the moment is to obtain the duration it took for a ticket to be resolved, via the Elapsedhrs column.

This is essentially a snippet of some of the data I'm looking at: -

If at all possible, the average calculation would need to be performed dynamically, as there are various filters available on the report that would need to have influence on it.

For example, the Assignedtoint column details which agent dealt with the ticket, these values are used as filters on the report. Due to this, there is a requirement for the average calculation to be peformed in conjunction with the chosen filter, which could be an array of different things, including average elapsedhrs over 'x' number of days.

Calculating the average like this, would be perfect, if we could get the values in the friendly formatted version of elapsed hours able to perform it: -

*I realise the above calculation/option would require the column to be set to a NUMBER format though, which I believe isn't feasible with the friendly format I'm wanting to present the values in. The general behaviour of the above calculation though is essentially what I'm needing, as it appears to react well to various filters. This average obviously works fine with my decimal version of elapsedhrs, it just isn't very friendly to read unfortunately !

I understand this could well be turning into something quite intensive, and if it is, I wouldn't expect anyone to put any substantial time into helping me, so please feel free to tell me to clear off !

Again, thank you so much for the guidance so far, it's been invaluable to me !!

[Rarreola]

Hi  I had de same problem a tried to conevert this taime to secunds then changed to dd hh mm ss, but i can´t get the average of that time, i have the same case is data from help desk, If you have a new idea write me.