Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I've got multiple dates in my report which show the different stages of the workflow. I want to measure how long it takes to get to each step but I don't want to include out of work hours. For example if one time is 26/08/2019 at 4pm and the next one is 27/08/2019 at 10 am I don't want the difference to be 18 hours, I want it to be 3 hours difference
Out work hours are 8:00am-6:00pm and I want to exclude weekends
How would I go about doing this?
Regards,
Mike
Hi @michael_knight ,
You can refer to the similar case: https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
Gave that a go. It works for one date to date but I'm unable to replicate the code and use it for another column.
Hour Difference - Lead Created On/Created On = VAR filtered = FILTER( ADDCOLUMNS( CROSSJOIN( CALENDAR( Opportunity[new_leadcreatedon], Opportunity[createdon]), SELECTCOLUMNS(GENERATESERIES(9,18), "Hour", [Value] ) ), "Day of the week", WEEKDAY([Date],2) ), [Day of the week] <6 ) VAR hourcount = COUNTROWS( FILTER( filtered, ( [Date] >= DATEVALUE( [new_leadcreatedon]) && [Hour] > HOUR( [new_leadcreatedon]) + 1 ) && ( [Date] <= DATEVALUE(Opportunity[createdon]) && [Hour] > HOUR(Opportunity[createdon]) - 1 ) ) ) VAR remained = DATEDIFF( TIMEVALUE(Opportunity[new_leadcreatedon]), TIME(HOUR( Opportunity[new_leadcreatedon]) + 1, 0, 0), MINUTE ) + DATEDIFF( TIME(HOUR(Opportunity[createdon]) - 1, 0, 0), TIMEVALUE(Opportunity[createdon]), MINUTE ) Return IF(hourcount <> BLANK(), (hourcount*60 + remained)/60,0)
I tried to use the same code but change the date fields but it's not working. Here's the code what the result is
Hour Difference - Created On/Date Last Assigned = VAR filtered = FILTER( ADDCOLUMNS( CROSSJOIN( CALENDAR( Opportunity[createdon], Opportunity[new_datelastassigned]), SELECTCOLUMNS(GENERATESERIES(9,18), "Hour", [Value] ) ), "Day of the week", WEEKDAY([Date],2) ), [Day of the week] <6 ) VAR hourcount = COUNTROWS( FILTER( filtered, ( [Date] >= DATEVALUE( [createdon]) && [Hour] > HOUR( [createdon]) + 1 ) && ( [Date] <= DATEVALUE(Opportunity[new_datelastassigned]) && [Hour] > HOUR(Opportunity[new_datelastassigned]) - 1 ) ) ) VAR remained = DATEDIFF( TIMEVALUE(Opportunity[createdon]), TIME(HOUR( Opportunity[createdon]) + 1, 0, 0), MINUTE ) + DATEDIFF( TIME(HOUR(Opportunity[new_datelastassigned]) - 1, 0, 0), TIMEVALUE(Opportunity[new_datelastassigned]), MINUTE ) Return IF(hourcount <> BLANK(), (hourcount*60 + remained)/60,0)
Also, do you know how I can make it so it only show the number to 2 decimal places on the column that works
Thanks,
Mike
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |