Convert time from decimal and then add up the total.

[Anonymous]

I looked at this: 

Solved: Convert from decimal to duration (HH:MM:SS) - Microsoft Power BI Community

And thought it worked

but then I realized it hadn't. My original problem is that I have a column of labor hours in my table. There are a few instances that go over 24 so PBI has an issue converting it to time. I decided to split the time into hours and minutes. I then add them back up in a column as ... "Duration", each ([Hours]+([Multiplier]/60))). There are instances of hours being negative. This is done to correct work entries that were entered incorrectly. So, when I split the hours and minutes, I have to redo the minutes and multiply by negative one if the hours are negative and positive 1 if they aren't. This gives me the decimal value in the Duration column of the table above. It was good enough until someone wanted to know how long was 16.33 really. 16 hours and 20 minutes is correct. But, 26.98 is not correct at 5 hours and 59 minutes. So the above cited solution didn't work. I also need to be able to sum these values for a matrix.  I've been at this for two days. I would appreciate any help. 

I did try to tweak this but got nowhere either.

[TomMartens]

Hey @Anonymous ,

please create a Power BI file that contains sample data, upload the pbix to onedrive or dropbox and share the link. If you are creatint the sample data using Excel, share the xlsx as well.

Please, describe the expected result based on the sample data you provide.

Regards,

Tom

[Anonymous]

The files are here for PBIX: https://www.dropbox.com/s/vgs9uiecjffprbx/Labor%20Report.pbix?dl=0

The Excel file is here: https://www.dropbox.com/s/mrabra542jqubi0/Labor%20Report.xlsx?dl=0

[TomMartens]

Hey @Anonymous , thanks for providing the sample files, I will have a closer look tomorrow, as I have to rest now.

Regards,

Tom

[Anonymous]

You'll see that in the Matrix column, I'm trying to add the total hours. I'd like it to show as hours and minutes and not as 2.30 (2 hours 30 minutes). I'd like to be able to get the sum amounts in there. The rest should be self explanatory. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

based on your sample file I created something.

For this, I first created a new column inside Power Query based on the column "Regular Hours" using the transformation "Total Seconds", the idea behind this, is to have a column that contains seconds. The next screenshot shows this:

Unfortunately, there are errors, so you have to either fix the values that result in to an error or create a column "on your own" that contains seconds.

Based on the column that contains the seconds I created this measure:

Duration = 

var _TotalSeconds = CALCULATE( SUM( 'Labor Report'[Total Seconds] ) )
return
if( NOT( ISBLANK( _TotalSeconds ) )

    ,var _Days = TRUNC(DIVIDE(_TotalSeconds , 3600 * 24 ) )
    var RemainingSecondsFromDay = MOD( _TotalSeconds , 3600 * 24 )
    var _Hours = TRUNC(DIVIDE( RemainingSecondsFromDay , 3600 ) )
    var RemaingSecondsFromHour = MOD( RemainingSecondsFromDay , 3600 )
    var _Minutes = TRUNC(DIVIDE( RemaingSecondsFromHour , 60 ) )
    var RemainingSecodndsFromHour = MOD( RemaingSecondsFromHour , 60 )
    return
    IF( _Days = 0
        , _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"
        , _Days & " days " & _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"
    )
    ,BLANK()
)

This may look more complex than it really is, the idea behind this is getting numeric values for days, hours, minutes, and seconds.

And finally, do some formatting 🙂 , the next screenshot show how the current formatting looks like:

These steps especially the "seconds" part are important as the DAX engine (the vertipaq engine) does not know the data type duration.

Hopefully, this provides what you are looking at least provides some new ideas.

Regards,

Tom