Display Time as a Duration Instead of Time of Day - i.e. Make Power BI Quit Changing the Source Data

[bvbull200]

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

[Anonymous]

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

[bvbull200]

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.