Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Thanks!
Solved! Go to 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:
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:
Hi,
This looks much better thank you! I just removed the seconds portion since it had a very large number of decimal places.
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
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.
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:
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |