i want to calculate the working hours that passed between the arrive of an email and the final answer to the client.
I found a very good script at this link https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374828#M170248 but there is a problem into it that i can't solve.
The formula starts to count the hours from the time in which the email arrived but it applies the same time for starting the count also on subsequent days; the fact is for the days in the middle i want the formula to take into consideration all the working hours (8:30 - 19:30). For example, if an email arrived at 12:25 of 29/03/19 the formula will count from that time (and it's fine), but it will do the same also for 30/03/19 (i want to change this).
Basically i want to add another line that manages the count for the days in the middle.
I was thinking to something like:
VAR hourcount =
( [Date] > DATEVALUE ( [ACTIVITY_DATE] )
&& [Hour] > HOUR ( [ACTIVITY_DATE] ) + 1 )
&& ( [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
&& [Hour] > HOUR ( [LASTMODIFIEDDATE] ) - 1 ) ) )
&&( [Date] > DATEVALUE ( [ACTIVITY_DATE] +1 )
&& [Hour] > HOUR ( 9:00 ) )
Could someone help me?
Work Hour = VAR filtered = FILTER ( ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ), SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] ) ), "Day of week", WEEKDAY ( [Date], 2 ) ), [Day of week] < 6 && [TicketID] = EARLIER ( Table1[TicketID] ) ) VAR hourcount = COUNTROWS ( FILTER ( filtered, ( [Date] >= DATEVALUE ( [ACTIVITY_DATE] ) && [Hour] > HOUR ( [ACTIVITY_DATE] ) + 1 ) && ( [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] ) && [Hour] > HOUR ( [LASTMODIFIEDDATE] ) - 1 ) ) ) VAR remained = DATEDIFF ( TIMEVALUE ( [ACTIVITY_DATE] ), TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ), MINUTE ) + DATEDIFF ( TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ), TIMEVALUE ( [LASTMODIFIEDDATE] ), MINUTE ) RETURN IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )
Hi @Roma28 ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Hi Frank @v-frfei-msft
thanks for the help !! I have sensible data in the original file, so i created a new one with just index, start & end date
Hi @Roma28 ,
I cannot access the link as you shared. Could you please share the files again by another way?
Try now ! I created a folder on Google Drive
Tell me if you have more trouble to access it