Duration in days, hours and minutes incorrect when output is negative

[BYENER]

Hi All,

I am calculating the duration between planned and actual inbound datetime. I am using the dateiff formula for this and I'm showing the output in this way : 1 days 2 hours 20 minutes. When the actual arival is earlier than planned the calculation will not work. It only working correctly when I use DATEDIFF(average(TBL_SHIPMENTS[Planned DateTime of Arrival]),average(TBL_SHIPMENTS[ACTUAL_ARRIVAL]),SECOND)  but I want to see in this way 1 days 2 hours 20 minutes

Measure = 

var vSeconds = DATEDIFF(average(TBL_SHIPMENTS[Planned DateTime of Arrival]),average(TBL_SHIPMENTS[ACTUAL_ARRIVAL]),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

vDays&" Days & "&

vRemainingHours&" Hours & "&

vRemainingMinutes&" Minutes & "&

vRemainingSeconds& " Seconds"

[BYENER]

@v-zhangti  thanks, this works fine!!

[v-zhangti · Accepted solution]

Hi, @BYENER 

According to the data you provided, there should be a difference of about 13 hours. To avoid the time becoming negative, you can add the function ABS(). https://docs.microsoft.com/en-us/dax/abs-function-dax 

Measure = 
var vSeconds = ABS(DATEDIFF(average(TBL_SHIPMENTS[Planned DateTime of Arrival]),average(TBL_SHIPMENTS[ACTUAL_ARRIVAL]),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
vDays&" Days & "&
vRemainingHours&" Hours & "&
vRemainingMinutes&" Minutes & "&
vRemainingSeconds& " Seconds"

Does this match the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.