Avg. Time Difference (Seconds) Between Events using a Customer ID and Unix Epoch Timestamp

Question

I have a set of data where I would like to track the amount of time in between stages using a Unix Epoch Timestamp and I would like to have it grouped by Customer ID.

The Data looks like the following sample:

Sample Data

Desired Result:

"Avg. Seconds Until Stage 2 = 21.3"

"Avg. Seconds from Stage 2 to Stage 3 = 8"

"Avg. Seconds from Stage 3 to Stage 4 = 16"

Some of the hurdles I'm running into is grouping by Customer ID as well as subtracting one specific Action's Unix Timestamp by another.

I'm happy with making three different measures or columns for each stage difference if that helps.

Any help would be very appreciated. Thank you.

Olly · Accepted Answer · 2019-02-12T10:52:13.687

Add a column to your query, to convert the Unix Epoch to a Date / Time:

= Table.AddColumn(#"Changed Type", "Timestamp", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Unix Epoch Timestamp]), type datetime)

If you don't already have a Customer dimension table, then create one:

Customers = SUMMARIZECOLUMNS ( Actions[Customer ID] )

Create a relationship on Customer ID between [Actions] and [Customers]

Now you can create the measures you want:

Seconds Until Stage 2 = 
VAR TimeStart = 
    MIN ( Actions[Timestamp] )
VAR TimeStage2 =
    CALCULATE ( 
        MIN ( Actions[Timestamp] ),
        Actions[Action] = "Moved to Stage 2"
    )
RETURN
    IF ( 
        HASONEVALUE ( Customers[Customer ID] ),
        DATEDIFF ( 
            TimeStart,
            TimeStage2,
            SECOND
        ),
        BLANK()
    )


Avg Seconds Until Stage 2 = 
AVERAGEX ( 
    Customers,
    [Seconds Until Stage 2]
)


Seconds Stage 2 to Stage 3 = 
VAR TimeStage2 =
    CALCULATE ( 
        MIN ( Actions[Timestamp] ),
        Actions[Action] = "Moved to Stage 2"
    )
VAR TimeStage3 =
    CALCULATE ( 
        MIN ( Actions[Timestamp] ),
        Actions[Action] = "Moved to Stage 3"
    )
RETURN
    IF ( 
        HASONEVALUE ( Customers[Customer ID] ),
        DATEDIFF ( 
            TimeStage2,
            TimeStage3,
            SECOND
        ),
        BLANK()
    )


Avg Seconds Stage 2 to Stage 3 = 
AVERAGEX ( 
    Customers,
    [Seconds Stage 2 to Stage 3]
)


Seconds Stage 3 to Stage 4 = 
VAR TimeStage3 =
    CALCULATE ( 
        MIN ( Actions[Timestamp] ),
        Actions[Action] = "Moved to Stage 3"
    )
VAR TimeStage4 =
    CALCULATE ( 
        MIN ( Actions[Timestamp] ),
        Actions[Action] = "Moved to Stage 4"
    )
RETURN
    IF ( 
        HASONEVALUE ( Customers[Customer ID] ),
        DATEDIFF ( 
            TimeStage3,
            TimeStage4,
            SECOND
        ),
        BLANK()
    )


Avg Seconds Stage 3 to Stage 4 = 
AVERAGEX ( 
    Customers,
    [Seconds Stage 3 to Stage 4]
)

Here's a worked example PBIX file: https://excel.solutions/so_54639352/

Avg. Time Difference (Seconds) Between Events using a Customer ID and Unix Epoch Timestamp

1 Answers1