cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aradhana
Frequent Visitor

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

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

4 REPLIES 4
PhilipTreacy
Super User
Super User

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 

 

tend.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


amitchandak
Super User
Super User

@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



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

@amitchandak,

 

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

 

Thanks,

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

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors