Forum Discussion

Bryanna's avatar
Bryanna
Helper II
3 years ago
Solved

Calculating Date/Time difference

Hi, I am trying to calculate the average between two dates/times using DAX. I am using the following formula and it is returning a negative number even though the difference is the same date and sho...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Bryanna 
     

    try this:

     

    duration_average = 
VAR _seconds=
AVERAGEX(
    TableName,
    DATEDIFF( TableName[DateRequested], TableName[DateClosed], SECOND)
)
VAR _secondsABS = ABS(_seconds)
VAR _day = TRUNC(_secondsABS/(24*60*60))
VAR _hour = TRUNC( MOD(_secondsABS, 24*60*60)/(60*60))
VAR _minute = TRUNC( MOD(_secondsABS, 60*60)/60)
VAR _second = MOD(_secondsABS, 60)
RETURN
IF(
    _seconds<=0,
    _day&" Day "& _hour&" Hour "& _minute&" Minute "&_second&" Second Ahead",
    _day&" Day "& _hour&" Hour "& _minute&" Minute "&_second&" Second Delay"
)

     

     

    i tried with such data:

    DateClosedDateRequested
    9/10/2022 11:00:089/10/2022 15:00:00
    9/12/2022 11:00:089/12/2022 13:00:00
    9/14/2022 19:00:089/14/2022 13:00:00

     

    it worked like this:

     

     

    The tricky part is the behaviour of TRUNC/INT/MOD for negative values. Lemme try to depict it in Excel:

     

Bryanna's avatar
Bryanna
Helper II
3 years ago

Hi!

 

I tried swapping as well but some still look odd. Still shows as a negative but they shipped it sooner than the requested ship date.

 

 

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi Bryanna 
 

try this:

 

duration_average = 
VAR _seconds=
AVERAGEX(
    TableName,
    DATEDIFF( TableName[DateRequested], TableName[DateClosed], SECOND)
)
VAR _secondsABS = ABS(_seconds)
VAR _day = TRUNC(_secondsABS/(24*60*60))
VAR _hour = TRUNC( MOD(_secondsABS, 24*60*60)/(60*60))
VAR _minute = TRUNC( MOD(_secondsABS, 60*60)/60)
VAR _second = MOD(_secondsABS, 60)
RETURN
IF(
    _seconds<=0,
    _day&" Day "& _hour&" Hour "& _minute&" Minute "&_second&" Second Ahead",
    _day&" Day "& _hour&" Hour "& _minute&" Minute "&_second&" Second Delay"
)

 

 

i tried with such data:

DateClosedDateRequested
9/10/2022 11:00:089/10/2022 15:00:00
9/12/2022 11:00:089/12/2022 13:00:00
9/14/2022 19:00:089/14/2022 13:00:00

 

it worked like this:

 

 

The tricky part is the behaviour of TRUNC/INT/MOD for negative values. Lemme try to depict it in Excel: