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):
https://1drv.ms/x/s!Akyp0hrzA6TBrU0VrP7N9AQkR9Cq?e=zYHpZc
