Another Time Duration Question

[AW1976NOVA]

Hi,

I know there are countless time duration questions on this forum but I am beyond confused at this point...especially when it comes to how to properly handle and more specifically visually chart time durations.

My situation is this:  I have an excel file that I'm bringing in to power bi desktop.  One of the column in the excel file contains time durations.  Here is example data:

Name    Date             Time

Joe        1/1/2020      01:56:39:257

Andy     2/3/2020      00:49:20.333

Tom      3/7/2020      00:00:15.257

Or in other words...for Joe:  1 hour, 56 minutes, and 39.257 seconds. 

This column is originally brought in to power bi as a general data type.  In transform data I then extract only the data before the decimal delimiter so for Joe I end up with 01:56:39.  Still in transform data, I then convert to a duration data type and Joe's 01:56:39 is still displayed as 01:56:39 in transform data. 

However, when I close and apply my changes 01:56:39 turns in to 0.0810069444444444444.

I have two questions:

1) why does this happen and how can I prevemt this from happening.

2) I want to sum these time durations in a bar chart by date.  Meaning I'd like to sum up the time durations for each day and display them in a way they make sense.  Currently they are summing but are displayed in a misleading and difficult way to interpret.  Ideally I'd like to sum the time durations and have them displayed in the typical DD:HH:MM:SS format.

Can someone assist?  I have read through many many threads and none seem to fully answer this exact question.

[rsbin · Accepted solution]

@AW1976NOVA,

Power BI and DAX do not handle time durations very well.  1:56:39, I believe is .0810 Days.

.0810 Days = 1.94 Hours.  You can then convert this into minutes and into seconds.

I normally convert durations into seconds.  Then use this:

WorkItemDuration_Format = 
VAR days = 
    ROUNDDOWN([WorkItemDuration] / 86400, 0 )
VAR hours =
    ROUNDDOWN ( MOD ( [WorkItemDuration], 86400) / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [WorkItemDuration], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [WorkItemDuration], 60 ) )
RETURN
    FORMAT(days,"00") & " days "
        & FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

I'm sure I pulled it from this Board a long time ago.

Hope this helps!