Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |