Forum Discussion
Calculate Duration from date time fields
Hi, yaman123
You can try the following methods.
1. First convert all intervals to seconds.
Seconds = DATEDIFF ( [Start time], [End time], SECOND )2. Then convert the seconds to hour-minute-second format.
Duration = INT ( [Seconds] / 3600 ) & ":" & INT ( ( [Seconds] - INT ( [Seconds] / 3600 ) * 3600 ) / 60 ) & ":" & [Seconds] - INT ( ( [Seconds] - INT ( [Seconds] / 3600 ) * 3600 ) / 60 ) * 60 - INT ( [Seconds] / 3600 ) * 3600Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can do it in both powerquery and dax
for simplicity I followed below steps in power query
1. added a column to calc hours
(Duration.Days([end date]-[start date])*24) + (Duration.Hours([end date]-[start date]))
2. added another col to calc minutes
Duration.Minutes([end date]-[start date])
3. added another col to calc seconds
Duration.Seconds([end date]-[start date])
4. then merge all columns like below
Text.Combine({Text.From([Hours], "en-US"), ":", Text.From([Seconds], "en-US"), Text.From([Minutes], "en-US"), ":", Text.From([Seconds], "en-US"), Text.From([Seconds], "en-US")})
below is the output
i am also attaching pbix file for your reference.
you can refer to below link as well for help
https://community.powerbi.com/t5/Desktop/Calculating-day-time-difference/m-p/321189