Calculated total time in workingdays/hours

[BassKroon]

Hello,

I have a problem, im trying to calculate the amount of time a Ticket is open before is has been closed. 

I want to know the amount of time a ticket has been open, but not include hours that are between 07:29AM - 06:01PM

So a column that gives me the hours worked on a ticket, friday 05:55PM Opened en Closed at monday 07:35AM will give me a 10 back in minutes. 

Lets say a day starts at 07:30AM and ends at 06:00PM, and i want to exclude the weekends. 

I have made a small sample of the data, so i hope you'll understand it better:

(in this sample, the start date is on a friday en the end date is the monday after the weekend)

Thanks, Bas

[Greg_Deckler]

I'll take a look at this, in the mean time, check out my NET WORK DAYS Quick Measure here which should solve part of the problem:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

Posting data as text is always better than screen shots. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Greg_Deckler]

I believe that this should do it:

Column = 
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR([Date_Start],[Date_End]),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
VAR __startHour = TIME(7,30,0)
VAR __endHour = TIME(18,0,0)
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDaysDuration = (__NetWorkDays - 2) * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR([Date_Start]),MINUTE([Date_Start]),SECOND([Date_Start]))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR([Date_End]),MINUTE([Date_End]),SECOND([Date_End]))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration

[Greg_Deckler]

Minor improvement, accounts for tickets that start and end on the same day.

mNetWorkDuration = 
// Get the start and end dates
VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])
// Calculate the Net Work Days between the start and end dates
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
// Set this to the start of the work day (7:30 AM)
VAR __startHour = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endHour = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration