cancel
Showing results 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.

Impactful Individual

## Rounding up time

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

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
Community Support

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.

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.

Community Support

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.

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.

5 REPLIES 5
Impactful Individual

As said other solutions welcome too.

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

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

Community Support

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.

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.

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 ?

Community Support

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.

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.

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors