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.
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?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |