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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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