Forum Discussion

Solution Sage

2 years ago

Solved

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) ?

Anonymous
2 years ago
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.
Anonymous
2 years ago
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

rpinxt
Solution Sage
2 years ago
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
- Anonymous
  Not applicable
  2 years ago
  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.
  - rpinxt
    Solution Sage
    2 years ago
    Nice! Anonymous
    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 ?

Related content