topic Re: Calculating Date/Time difference in DAX Commands and Tips

Calculating Date/Time difference

Bryanna — Fri, 09 Dec 2022 15:42:43 GMT

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 should be -0 day 1 hr 25 mins for the first highlighted example. What am i doing wrong?

duration_average3 =

VAR total_second =

AVERAGEX(

'90 Day Order Patterns Time',

DATEDIFF('90 Day Order Patterns Time'[Date Closed],'90 Day Order Patterns Time'[Date Requested to Ship],SECOND)

)

VAR DAXDay = INT(total_second/(24*60*60))

VAR DAXHours = MOD(INT(total_second/(60*60)),24)

VAR DAXMin = MOD(INT(total_second/60),60)

RETURN DAXDay &" Day "& DAXHours &" Hour "& DAXMin &" Minute "

Thanks!

Re: Calculating Date/Time difference

FreemanZ — Fri, 09 Dec 2022 17:11:26 GMT

hi Bryanna

the arguments for DATEDIFF seem reversed. Try swap places.

Re: Calculating Date/Time difference

Bryanna — Fri, 09 Dec 2022 18:14:50 GMT

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.

Re: Calculating Date/Time difference

ppm1 — Sat, 10 Dec 2022 01:15:22 GMT

Please see this article for a better way to deal with dates/times/durations in DAX.

Calculate and Format Durations in DAX – Hoosier BI

Pat

Re: Calculating Date/Time difference

FreemanZ — Sat, 10 Dec 2022 05:01:24 GMT

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:

DateClosed	DateRequested
9/10/2022 11:00:08	9/10/2022 15:00:00
9/12/2022 11:00:08	9/12/2022 13:00:00
9/14/2022 19:00:08	9/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:

Re: Calculating Date/Time difference

Bryanna — Mon, 12 Dec 2022 13:11:31 GMT

Hi,

This looks much better thank you! I just removed the seconds portion since it had a very large number of decimal places.