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

[lawada]

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

[Fowmy]

@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 🙂

YouTube  LinkedIn

[lawada]

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

[CNENFRNL]

@lawada , you might want to try an interesting trick