Forum Discussion
Rounding up time
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ?
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.