How to get duration in hours between 2 date/time

[Aftab]

Hello Everyone,

I am new to PowerBi and need help finding a solution to the below problem.

I have 2 columns in my excel datasource, 'Created Date Time' and 'Time Resolved', I need help creating a column or a measure to calculate the difference between these to date-Time, and need the desied output to be in hh:mm:ss format.

I can easily do that in excel, by subtracting the start date/time from end date/time and then changing the cell format to Time, however finding it difficult to achieve in PowerBi.

Below is a screenshot from excel with desired Output.

Data:

Category	Created Date Time	Time Resolved	Resolve Duration (Desired Output)
Task 1	8/3/2018 10:44	8/3/2018 14:56	4:12:00
Task 1	8/1/2018 11:58	8/1/2018 17:12	5:14:00
Task 1	8/22/2018 14:18	8/23/2018 10:50	20:32:00
Task 1	8/23/2018 17:33	8/24/2018 14:34	21:01:00
Task 1	8/7/2018 17:22	8/8/2018 15:49	22:27:00
Task 1	8/9/2018 14:04	8/29/2018 14:32	480:28:00
Task 1	8/2/2018 17:03	8/29/2018 12:14	643:11:00
Task 2	8/2/2018 8:14	8/29/2018 17:27	657:13:00
Task 2	8/2/2018 12:50	8/2/2018 13:02	0:12:00
Task 2	8/3/2018 9:07	8/27/2018 11:23	578:16:00
Task 2	8/14/2018 12:49	8/15/2018 14:28	25:39:00
Task 2	8/14/2018 15:43	8/14/2018 15:45	0:02:00
Task 2	8/14/2018 16:51	8/14/2018 17:05	0:14:00
Task 2	8/15/2018 12:41	8/21/2018 16:06	147:25:00
Task 2	8/16/2018 8:49	8/21/2018 10:08	121:19:00

When Pivotted.

[v-yuta-msft · Accepted solution]

Hi Aftab,

To achieve your requirement, create a calculate column using DAX as below:

Result = 
VAR interval = DATEDIFF(Table1[Created Date Time], Table1[Time Resolved], SECOND)
VAR hour = INT(interval / 3600)
VAR minute = INT((interval - hour * 3600) / 60)
VAR second = MOD(interval, 60)
RETURN
HOUR & ":" & minute & ":" & second

  

Regards,

Jimmy Tao

[Greg_Deckler]

Date/Time column in Power BI is really just a decimal value where the integer portion is the number of days and the decimal portion is the time component. You could potentially use DATEDIFF with the HOUR option. Or do it manually, here is an old article on aggregating duration in Power BI. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486