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

[Anonymous]

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.

[v-frfei-msft]

Hi @Anonymous,

Please update your third formula as below.

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

[Anonymous]

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

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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 ()
                    && Test[LW_RECEIVE_START_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 ()
                    && Test[LW_RECEIVE_START_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

[v-frfei-msft]

Hi @Anonymous,

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

Regards,
Frank