cancel
Showing results for
Did you mean:
Member

## calculate average of seconds difference between 2 dates and format the average

hi,

I need to implement the below logic in power bi:

If (JMS_DELIVERED_DATE_TIME is not NULL) and (FIRST_TOUCH_DATE_TIME is not NULL) and (LW_RECEIVE_START_DATE_TIME  is not NULL ) then AVG (JMS_DELIVERED_DATE_TIME  - FIRST_TOUCH_DATE_TIME )

So to implement the above logic I am using below expression in dax

1.First, calculate the seconds difference between 2 dates:

Gateway_Time_Diff = IF(Test[JMS_DELIVERED_DATE_TIME]<>BLANK() && Test[FIRST_TOUCH_DATE_TIME]<>BLANK() && Test[LW_RECEIVE_START_DATE_TIME]<>BLANK(), DATEDIFF(Test[FIRST_TOUCH_DATE_TIME],Test[JMS_DELIVERED_DATE_TIME],SECOND))

2.Second: calculate the average

GatewayTimeAvg = averagex(Test,Test[Gateway_Time_Diff])

3.Third: formatting sends in hh:minutes:secods format

GatewayTime =
var DAXDay   = INT(Test[GatewayTimeAvg]/(24*60*60))
var DAXHours = MOD(INT(Test[GatewayTimeAvg]/(60*60)),24)
var DAXMin   = MOD(INT(Test[GatewayTimeAvg]/60),60)
var DAXSec   = MOD(Test[GatewayTimeAvg],60)
return
FORMAT(DAXHours,"#00")&":"&FORMAT(DAXMin,"#00")&":"&FORMAT(DAXSec,"#00")

so please advise me if I am correct in this case? as because the average time is shwing as 9:30:37 only.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: calculate average of seconds difference between 2 dates and format the average

Hi @dsandip59,

To use the combined one as below.

```Column =
VAR a =
CALCULATE (
SUMX (
FILTER (
ALL ( Test ),
Test[JMS_DELIVERED_DATE_TIME] <> BLANK ()
&& Test[FIRST_TOUCH_DATE_TIME] <> BLANK ()
),
DATEDIFF ( Test[FIRST_TOUCH_DATE_TIME], Test[JMS_DELIVERED_DATE_TIME], SECOND )
)
)
VAR b =
a
/ CALCULATE (
COUNTROWS ( Test ),
FILTER (
ALL ( Test ),
Test[JMS_DELIVERED_DATE_TIME] <> BLANK ()
&& Test[FIRST_TOUCH_DATE_TIME] <> BLANK ()
)
)
VAR DAXDay =
INT ( b / ( 24 * 60 * 60 ) )
VAR DAXHours =
MOD ( INT ( b / ( 60 * 60 ) ), 24 )
VAR DAXMin =
MOD ( INT ( b / 60 ), 60 )
VAR DAXSec =
MOD ( b, 60 )
RETURN
DAXDay & "day "
& FORMAT ( DAXHours, "#00" )
& ":"
& FORMAT ( DAXMin, "#00" )
& ":"
& FORMAT ( DAXSec, "#00" )
```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
4 REPLIES 4
Community Support Team

## Re: calculate average of seconds difference between 2 dates and format the average

Hi @dsandip59,

```GatewayTime =
var DAXDay   = INT(Test[GatewayTimeAvg]/(24*60*60))
var DAXHours = MOD(INT(Test[GatewayTimeAvg]/(60*60)),24)
var DAXMin   = MOD(INT(Test[GatewayTimeAvg]/60),60)
var DAXSec   = MOD(Test[GatewayTimeAvg],60)
return
DAXDay &"day "& FORMAT(DAXHours,"#00")&":"&FORMAT(DAXMin,"#00")&":"&FORMAT(DAXSec,"#00")```

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: calculate average of seconds difference between 2 dates and format the average

Hi,

As I have seen that you only have included the 'Day' in my existing format, but my question was "Is my DAX expression was correct?" and Can it be done in a single expression without creating 3 custom column?

Thanks,

Sandip

Highlighted
Community Support Team

## Re: calculate average of seconds difference between 2 dates and format the average

Hi @dsandip59,

To use the combined one as below.

```Column =
VAR a =
CALCULATE (
SUMX (
FILTER (
ALL ( Test ),
Test[JMS_DELIVERED_DATE_TIME] <> BLANK ()
&& Test[FIRST_TOUCH_DATE_TIME] <> BLANK ()
),
DATEDIFF ( Test[FIRST_TOUCH_DATE_TIME], Test[JMS_DELIVERED_DATE_TIME], SECOND )
)
)
VAR b =
a
/ CALCULATE (
COUNTROWS ( Test ),
FILTER (
ALL ( Test ),
Test[JMS_DELIVERED_DATE_TIME] <> BLANK ()
&& Test[FIRST_TOUCH_DATE_TIME] <> BLANK ()
)
)
VAR DAXDay =
INT ( b / ( 24 * 60 * 60 ) )
VAR DAXHours =
MOD ( INT ( b / ( 60 * 60 ) ), 24 )
VAR DAXMin =
MOD ( INT ( b / 60 ), 60 )
VAR DAXSec =
MOD ( b, 60 )
RETURN
DAXDay & "day "
& FORMAT ( DAXHours, "#00" )
& ":"
& FORMAT ( DAXMin, "#00" )
& ":"
& FORMAT ( DAXSec, "#00" )
```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Support Team

## Re: calculate average of seconds difference between 2 dates and format the average

Hi @dsandip59,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!