sum of time duration

[Trebor84]

Hi,

I have an issue with a straight forward time calculation.

I have a table with a start and end time then a calculated column in power query to deduct end time from start time.

I am trying to display this as a time duration in a visual but it seems to default to decimal.

Ideally I would have a dax measure to do this rather than a calculated column.  Can somone have a look at my file please to see if there is a solution?

https://easyupload.io/keegya 

[v-angzheng-msft]

Hi, @Trebor84 

I'm sorry I can't download the file you uploaded, it shows an alert of an internet error, actually my internet is working fine
As you can see from your attached picture, your Hours column is multiplied by 60, actually it only needs to be multiplied by 24 as @mahoneypat  explained, I think that's why your result is different from mine

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

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

[v-angzheng-msft · Accepted solution]

Hi, @Trebor84 

There are many ways to get the duration to be displayed, such as subtracting the time in a power query, and then changing the data type to text.

After my attempts, it is difficult to display the time difference as duration in the cluster bar chart visual,

unless it is in text format, but the text format does not compare the value size correctly.

A measure cannot be a value field in a cluster bar chart. So a column may be needed

One way that could work would be to make the duration in hour units.

Hours = Duration *24

Sample:

Result:

If you still insist on using the duration format, it's a good idea to change the visual you're trying to display.(Like table, Multi-row card…)

Hope i didn't make the problem more complicated.

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

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

[Trebor84]

Hi @v-angzheng-msft 

I think the Hours = Duration *24 is the way I would like to go with this. Can you take another look at my file please.  I must be missing something really obvious with this.

https://filebin.net/sfbegwwtz4dh40pt/

[mahoneypat]

Your picture shows you are multiplying by 60 when in your text you stated *24.  The decimal form of duration is in days so *24 is correct.

Pat

[amitchandak]

@Trebor84 ,

[end time] - [start time] will give you diff in duration in a column but will not add up  

You can get in seconds 

diff= datediff([start date],[end date], second)  //new column

measure  =

var Seconds1 = sum(Table[Diff])

return

quotient(Seconds1,3600 ) & ":" & FORMAT ( quotient(mod(Seconds1,3600 ),60), "00" ) & ":" & FORMAT ( mod(mod(Seconds1,3600 ),60), "00" )

also check for  Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389