Forum Discussion

lawada's avatar
lawada
Icon for Helper III rankHelper III
5 years ago

duration between 2 dates with time (AM/PM)

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

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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

     

     

  • abodqm's avatar
    abodqm
    Regular Visitor
    2 years ago

     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