Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |