duration between 2 dates with time (AM/PM)

[lawada]

i need a quick help with this:

im trying to find the duration between two dates with dax however theres something wrong with the formula as  calculating time not correctly and its not considering when the time in the date is AM or PM

first,i used the formula the calculate the diffrenece between the two dates in seconds:

Duration in Seconds= datediff(order pickup date, order cancelation date, second)

after that i wanted to find the duration separated into (days,hours,minutes,seconds) so i used this formula:

duration =

var d= SUM(orders[Duration in Seconds])

var vMinutes=int( d/60)

var vRemainingSeconds=MOD(d, 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"

this is an example of how im getting the result:

for the highlted row , its calculating -1 day however result should be  0 hours, 48 minutes and 7 seconds

[abodqm]

 i can i sum all time to show total with this measure ??? if i want to se a client orders and see times taken for example i want to sum the time with this measure

[Fowmy]

@lawada 

You can use a single measure without having to create a column, please try below as a measure:

Duration Between Cancell and Pickup = 
var vSeconds=DATEDIFF(MAX(Orders[order pickup date]),MAX(Orders[order cancelation date]),SECOND)
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
IF( 
   vSeconds > 0 , 
    vDays&" Days & "&
    vRemainingHours&" Hours & "&
    vRemainingMinutes&" Minutes & "& 
    vRemainingSeconds& " Seconds"
  ,
    BLANK()
)

________________________

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

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

Website YouTube  LinkedIn