Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bryanna
Helper II
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 "

 

Bryanna_0-1670600362878.png

 

Thanks!

1 ACCEPTED SOLUTION

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:

FreemanZ_0-1670648266656.png

 

FreemanZ_1-1670648291560.png

 

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

FreemanZ_2-1670648425606.png

 

View solution in original post

5 REPLIES 5
Bryanna
Helper II
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.

 

Bryanna_0-1670850661464.png

 

ppm1
Solution Sage
Solution Sage

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

Microsoft Employee
Bryanna
Helper II
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.

 

Bryanna_0-1670609630296.png

 

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:

FreemanZ_0-1670648266656.png

 

FreemanZ_1-1670648291560.png

 

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

FreemanZ_2-1670648425606.png

 

FreemanZ
Super User
Super User

hi @Bryanna 

the arguments for DATEDIFF seem reversed. Try swap places.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors