Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to calculate the number of working hours/minutes between two dates (ie. excluding non-working hours and weekends).
This is the basic format - and I'm trying to find out how long it takes for an issue to get resolved, in working hours/minutes. Can anyone help, please?
Issue id | Created | Resolved |
1265400 | 03/03/2020 13:54 | 03/03/2020 14:21 |
1265256 | 03/03/2020 12:54 | 03/03/2020 13:45 |
1263801 | 03/03/2020 09:09 | 03/03/2020 09:11 |
1263789 | 03/03/2020 08:42 | 03/03/2020 10:50 |
1262321 | 02/03/2020 17:12 | 03/03/2020 09:18 |
1261985 | 02/03/2020 11:54 | 02/03/2020 11:57 |
1258735 | 28/02/2020 22:27 | 02/03/2020 16:53 |
1258687 | 28/02/2020 16:26 | 03/03/2020 10:12 |
1258638 | 28/02/2020 14:54 | 02/03/2020 16:00 |
I found the solution in this post really clear and helpful: https://addendanalytics.com/calculate-working-hours-in-power-bi/
Hi,
Looking at calculate minutes between a ticket was created and closed.
The measure needs to consider the following:
- Should ignore public holidays (Have a table called public holidays in which the date column has the days I have as public holidays)
- Should consider Mon to Friday
- Business Hours 7 am to 5 pm
- If completed is greater than created then should take it as 0
Step created at AEDT Step Completed at AEDT
15/6/21 11:32:24 am 16/6/21 2:22:46 pm
Any help will be appreciated
See if these help:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306
@Greg_Deckler - I tried your net workdays option and it worked (thankfully no negative numbers output)! Thanks!
I changed the output to hours, which works better for me - although this seems to round off rather than offering part hours. Do you know if there's a way to have the output in part-hours (eg. 1.25 for 1hr12mins instead of 1)?
😞 still gives me whole hour output (.00).
Thanks Greg - it seems the Net Work Days option should be close to what I want. Although I will have the same issue as the other user who commented in the thread (start and finish times potentially occurring out of work hours). Was there a solution for avoiding the negative numbers?
Thanks again!
You may calculate working hours/minutes since Mon, Feb 24, 2020.
DIFF ( <Base>, <Resolved> ) - DIFF ( <Base>, <Created> )
Thanks for your response, @v-chuncz-msft. I've not seen this option - are there any articles to explain the function?
What would be the <Base> value here?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |