cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bryanna
Helper I
Helper I

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
FreemanZ
Community Champion
Community Champion

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 I
Helper I

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 I
Helper I

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

 

FreemanZ
Community Champion
Community Champion

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
Community Champion
Community Champion

hi @Bryanna 

the arguments for DATEDIFF seem reversed. Try swap places.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors