cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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 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!

1 ACCEPTED SOLUTION
Super User

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:

5 REPLIES 5
Helper II

Hi,

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

Solution Sage

Calculate and Format Durations in DAX – Hoosier BI

Pat

Microsoft Employee
Helper II

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.

Super User

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:

Super User

hi @Bryanna

the arguments for DATEDIFF seem reversed. Try swap places.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors