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 am excessively frustrated at Power BI's complete inability to calculate the business hours between two instances. If I provide July 1, 2019 12:00 p.m. and July 2, 2019 9 a.m. It should be able to figure out that this is 6 business hours (5 hours from July 1, 2019 12:00 p.m. to 5:00 p.m. and 1 hour from July 2, 2019 8:00 a.m. to 9:00 a.m.). It can't and I've resigned myself to this fact.
Excel can, however, so I've done the math there. Enter my next monumental frustration.
I am providing the number of hours between those two instances to Power BI as hh:mm:ss. Excel does the work, then populates a table with 6:00:00 in the scenario above. Power BI, however, insists on calculating this as 6:00:00 a.m.. This causes massive issues when the number of hours exceeds 12 or 24. 14:15:20 is displayed as 2:15:20 p.m. instead of 14 hours, 15 minutes, and 20 seconds.
To potentially get around this, I have provided the data in another way. I have calculated the number of business days that the time between two scenarios takes. A business day, in this case, is 9 hours, so, if something took 9 hours, the result would be 1 (1 business day = 9 hours). If it took 4.5 hours, the result would be 0.5. If it took 18 hours, the result would be 2. I think you get the idea.
With this, I need to conver the number of business days back in to the hh:mm:ss format. I would need to be able to provide the number 1.908 and it give me 17:10:19 (17 hours, 10 minutes, and 19 seconds). This will need to accomodate instances over 24 hours as well. 3.471 should convert to 31:14:13 (31 hours, 14 minutes, and 13 seconds).
I have provided sample source data on One Drive and given the links below so you can see what I am working with. I have scoured the internet for DAYS and cannot find anything close to what I'm looking for. I find it staggering that a relatively simple thing to do in Excel completely befuddles a supposed Business Intelligence powerhouse like Power BI. I also find it exceedingly frustrating that there is NO way to preserve the format of the data that you provide Power BI. If I put in 31:25:10, it is because I want 31:25:10 and not whatever date/time calculation Power BI assumes is necessary.
Long post, I know, but thank you for anyone that can provide insight to this. The links are below.
Power BI Sample:
https://1drv.ms/u/s!Akyp0hrzA6TBrU7tzFdwP92UzlOE
Excel Sample Data (this is populating the Power BI sample):
Hey @bvbull200
Have you searched the community?
https://community.powerbi.com/t5/Desktop/Calculate-Hours-between-dates-for-only-business-hours/td-p/...
https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255
https://community.powerbi.com/t5/Desktop/Working-days-between-two-dates-simply/td-p/94172
Cheers!
A
I have searched exensively. I have not found a solution that addresses this issue.
The first link you posted seems close. I have not come across that thread, yet. I will try to modify it to my needs.
The issue with the others is that it is a whole number returned rather than a duration. I have yet to find a way to reliably convert the whole number in to a duration that can then be summarized in some way (average, for instance).
Crazy how many lines of code are needing to be written to perform what a simple formula in Excel does.
Thanks for the links.
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 |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |