Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
rpinxt
Impactful Individual
Impactful Individual

Rounding up time

I have these date/time staps and the duration between them :

rpinxt_0-1712150501700.png

 

Duration is a dax measure being :

Duration = MAX(SC1[D&T]) - MAX(Sheet1[ATA])
 
The outcome is then formatted like (hh:nn:ss)
As you see works like a charm.
 
But now I want to check what all took longer than 24 hours.
I was thinking making a field Hours to round Duration to the closest hour and than have the ability to do a simple check :
if hours > 24 , "Late", "On Time"
 
To make hours I wanted to use :
Hours = MROUND ([Duration], TIME (1,0,0)) + TIME (0,0,0)
 
But this gives me an error saying:
An argument of function MROUND has the wrong data type or the result is too large or too small.
 
Does anybody know how to fix?
Or how would you guys check if the duration was more than 24 hours (so 1 day) ?

 

2 ACCEPTED SOLUTIONS

Hi @rpinxt ,

 

As far as I know, measure in time format like Duration = MAX(SC1[D&T]) - MAX(Sheet1[ATA]) has a limitation of 24 hour. So if your duration is 24:01:00, Power BI will only return 00:01:00.

Here I suggest you to try DATEDIFF function.

Flag = 
VAR _HOURDIFF = DIVIDE(DATEDIFF(MAX(Sheet1[ATA]),MAX(SC1[D&T]),MINUTE),60)
RETURN
IF(_HOURDIFF>24,"Late","On Time")

Result is as below.

vrzhoumsft_0-1712198516769.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Hi @rpinxt ,

 

As far as I know, the time format data (like hh:mm:ss) in Power BI has the 24 hours limitation.

If you want to show hh:mm:ss data outer 24 hours, I suggest you to try code as below.

Please note that in this way your Duration will be in text data format.

Duration New = 
VAR _SECDIFF = DATEDIFF(MAX(Sheet1[ATA]),MAX(SC1[D&T]),SECOND)
VAR _HOUR =INT(DIVIDE(_SECDIFF,3600))
VAR _MIN = INT(DIVIDE(MOD(_SECDIFF,3600),60))
VAR _SEC = _SECDIFF - _HOUR * 3600 - _MIN * 60
RETURN
COMBINEVALUES(":",FORMAT(_HOUR,"00"),FORMAT(_MIN,"00"),FORMAT(_SEC,"00"))

Result is as below.

vrzhoumsft_0-1712299295073.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
rpinxt
Impactful Individual
Impactful Individual

As said other solutions welcome too. 

Because the end goal is to see how much Qty percentage for a shipment was late :

rpinxt_0-1712152674057.png

 

So if for example shipment 194272 the first 2 lines where late and you would look at a ShipmentNr level,

(22+37) / 210 would be 28,1% late

 

 

Hi @rpinxt ,

 

As far as I know, measure in time format like Duration = MAX(SC1[D&T]) - MAX(Sheet1[ATA]) has a limitation of 24 hour. So if your duration is 24:01:00, Power BI will only return 00:01:00.

Here I suggest you to try DATEDIFF function.

Flag = 
VAR _HOURDIFF = DIVIDE(DATEDIFF(MAX(Sheet1[ATA]),MAX(SC1[D&T]),MINUTE),60)
RETURN
IF(_HOURDIFF>24,"Late","On Time")

Result is as below.

vrzhoumsft_0-1712198516769.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

rpinxt
Impactful Individual
Impactful Individual

Nice! @v-rzhou-msft 

I did not even see / realise that when it is more than 24 hours it would "reset".

That is why I never saw lines that had more than 24 hours.....

So we can only show the hours difference?

 

It is not that we can say if something was 26 hours, 18 minutes and 30 seconds late to show 26:18:30 ?

Hi @rpinxt ,

 

As far as I know, the time format data (like hh:mm:ss) in Power BI has the 24 hours limitation.

If you want to show hh:mm:ss data outer 24 hours, I suggest you to try code as below.

Please note that in this way your Duration will be in text data format.

Duration New = 
VAR _SECDIFF = DATEDIFF(MAX(Sheet1[ATA]),MAX(SC1[D&T]),SECOND)
VAR _HOUR =INT(DIVIDE(_SECDIFF,3600))
VAR _MIN = INT(DIVIDE(MOD(_SECDIFF,3600),60))
VAR _SEC = _SECDIFF - _HOUR * 3600 - _MIN * 60
RETURN
COMBINEVALUES(":",FORMAT(_HOUR,"00"),FORMAT(_MIN,"00"),FORMAT(_SEC,"00"))

Result is as below.

vrzhoumsft_0-1712299295073.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rpinxt
Impactful Individual
Impactful Individual

Looks very good @v-rzhou-msft !

Text should not be a problem as we would not have to calculate with that "time" field again.

So it woud only be for displaying.

Thanks.

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.