calculate hours spent on a ticket before 12 AM for ticket start time

[Anonymous]

How can I calculate hours spent on a ticket before 12 AM for ticket start time

I have the below column for which I need to calculate the hours spent before 12 AM so that I can calculate the hours spent on the ticket for one specific date if the ticket end date is after 12AM as well.

Regards,

GKK

@Eric_Zhang @amitchandak @AlB 

[AlB]

@Anonymous 

Create two calculated columns in your table and set their data type to Time 

Time 1st day = DATEVALUE(Table1[end_time]) - Table1[start_time]  

Time 2nd day = Table1[end_time] - DATEVALUE(Table1[end_time])   

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

The result I need is Ticket end time on or before 23:59:59 PM on a specific date.

Example:- if a sepcific ticket is worked from 25/11/2020 9:15 PM to 26/11/2020 3:15 AM

Ticket end time on or before 23:59:59 PM should come under 25/11/2020 and on or after 12 AM should be calculated under 26/11/2020.

Ticket ID start_time end_time
1 02/09/2020 17:04 03/09/2020 02:17
2 17/05/2020 20:20 18/05/2020 05:29
3 11/08/2020 20:10 12/08/2020 05:19
4 01/07/2020 13:31 01/07/2020 22:40
5 21/09/2020 13:24 21/09/2020 22:35
6 27/09/2020 10:55 27/09/2020 20:15
7 05/08/2020 13:55 05/08/2020 23:03
8 16/07/2020 13:56 16/07/2020 23:05
9 24/07/2020 13:58 24/07/2020 23:08
10 21/08/2020 10:00 21/08/2020 19:14
11 23/06/2020 21:01 24/06/2020 06:16
12 25/06/2020 20:49 26/06/2020 06:03
13 20/08/2020 21:03 21/08/2020 06:11
14 24/07/2020 11:00 24/07/2020 20:13
15 01/10/2020 10:54 01/10/2020 20:07
16 01/05/2020 23:59 02/05/2020 09:09
17 29/09/2020 22:17 30/09/2020 07:28
18 05/03/2020 23:39 06/03/2020 08:58
19 09/01/2020 13:38 09/01/2020 22:51

This is what I am trying to achieve, if you can help me with this 

[Anonymous]

@AlB 

[AlB]

@Anonymous 

I asked for an example based on the data with the expected result to help clarify. You have not provided it and your text explanation is not clear enough for me to understand.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

Thank you for replying.

Please see below the data.

start_time	end_time
26/02/2020 21:59	26/02/2020 21:59
25/02/2020 15:14	25/02/2020 15:14
08/07/2020 19:00	08/07/2020 19:00
17/08/2020 14:00	17/08/2020 14:00
18/08/2020 14:00	18/08/2020 14:00
19/08/2020 19:26	19/08/2020 19:26
17/08/2020 19:06	17/08/2020 19:06
18/08/2020 19:31	18/08/2020 19:31
20/08/2020 14:00	20/08/2020 14:00
20/08/2020 19:39	20/08/2020 19:39
31/08/2020 19:11	31/08/2020 19:11

I need the start time before 12 AM and End time after 12 AM if the same ticket or Job id is worked upon after 12 AM as well.

AS I need to calculate the production availability of agents for each day before 12 AM and the rest of the time after 12 AM even for the same ticket should be calculated in the next day.

[AlB]

Hi @Anonymous 

I don't understand the question. Please clarify with an example based on sample data. And please share the data on a format that can be copied rather than on screen cap

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Anonymous , how we plan to take diff. Diff from for hour spend.

Also refer these

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389