Need to calculate the datetime difference between start and end datetime values

[Aradhana]

Hi All,

I need to calculate the difference between tstart and tend values as a downtime and show it under 10 min duration category in x axis.

For example, If I need to show the time difference between 7:30 to 7:40, then I need to consider rows from numbeer 3 to 8 and sum it up..same way 7:40 to 7:50 means number 8 to 9.

Sl.no     TStart                              TEnd                                  Minutes
1          01/31/2022 19:14:32        01/31/2022 19:14:42       19:10
2          01/31/2022 19:28:38        01/31/2022 19:29:43       19:20
3          01/31/2022 19:33:24        01/31/2022 19:33:30       19:30
4          01/31/2022 19:33:34        01/31/2022 19:33:56       19:30
5          01/31/2022 19:34:01        01/31/2022 19:34:02       19:30
6          01/31/2022 19:34:06        01/31/2022 19:34:07       19:30
7          01/31/2022 19:34:12        01/31/2022 19:34:14       19:30
8          01/31/2022 19:34:36        01/31/2022 19:44:58       19:30
9          01/31/2022 19:45:28        01/31/2022 19:46:13       19:40

hope the question is clear.

Thanks in advance.

Thanks,

Aradhana

[PhilipTreacy]

Hi @Aradhana 

I'm not quite sure what you want as a result, but, perhaps you could calculate the difference between TEnd and TStart and then group by the Minutes column, summing the differences you just calculated with TEnd-TStart 

Download my example PBIX file. 

Please note it has a number of steps in the query that I had to create to tidy up the data that was pasted here.  You won't need those steps, just these

#"Added Custom" = Table.AddColumn(#"Changed Type with Locale1", "Custom", each [TEnd]-[TStart]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Minutes"}, {{"Time", each List.Sum([Custom]), type duration}})
in
    #"Grouped Rows"

Regards

Phil

[amitchandak]

@Aradhana , You can date diff in time like

diff = [TEnd] -[Tstart]

But not easy to add time

better have time in minute or second

diff in minute = datediff([TStart] ,[TEnd], Minute)

You put filter in measure get specific duartion

[Aradhana]

@amitchandak,

I'm already taking difference in seconds but not able to sum it up based on the requirement.

Thanks,

Aradhana

[Aradhana]

@Philip,

I need to show 10 min break up in x axis.

diference of tstart and tend should be less than or equal to 600 seconds.

If you consider my example table, it should find the difference of 7:34:36 to 7:40:00 for 8th row and consider rest 0:04:58 to 9th row.

Thanks,

Aradhana