Forum Discussion
Calculated total time in workingdays/hours
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 th...
Greg_Deckler
Community Champion
Community ChampionI'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