cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

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

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

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

Highlighted
Helper II
Helper II

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

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. 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors