cancel
Showing results for
Search instead for
Did you mean:
Helper I

## calculate average delay in (days,hours,minutes,seconds)

i have to calculate the Average delay in orders in (days,hours,minutes,seconds)

first i calculated the duration in seconds :

Duration in Seconds =
duration=DATEDIFF(orders[Order PlacedDate],orders[In Progress Order Date],SECOND)

then i calculated the count of orders
Orders=Count(Orders[id])

after that i found the average delay in seconds :
averge = SUM(orders[Duration in Seconds])/[orders]

after that i found the average in HH:MM:SS using this formula which i found also in the community
avg HH:MM:SS =
// We start with a duration in number of seconds

// There are 3,600 seconds in an hour
VAR Hours =
INT ( [averge] / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( [averge] - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( [averge] - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
)

And now i have to use this formula to calculate the Average delay in Days , Hours , minutes, Seconds

any suggestions please
3 REPLIES 3
Super User IV

@lawada

I have modified your measure and you use the follwing :

``````Avg Delay =
var vSeconds =
DIVIDE(
DATEDIFF(orders[Order PlacedDate],orders[In Progress Order Date],SECOND),
Count(Orders[id])
)
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
vDays&" Days & "&
vRemainingHours&" Hours & "&
vRemainingMinutes&" Minutes & "&
vRemainingSeconds& " Seconds"``````

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn
Helper I

thank you this calculation worked for me, however im getting the seconds with decimilas how can i get rid of the decimals ?

Super User III

@lawada , you might want to try an interesting trick

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors