Forum Discussion

Advocate I

8 years ago

Solved

Calculated Column - time between events in different rows

I am trying to calculate the time since the last similar event per user. The data set has UserIds, Event types, and a date/time. In events there are open, save, and various other event types. I'm loo...

Need Help

Zubair_Muhammad

8 years ago

Hi jpurcell

These Calculated Columns will also give you desired results

First an Index Column

Index =
IF (
    OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ),
    RANKX (
        FILTER (
            ALL ( TableName ),
            OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" )
                && TableName[UserID] = EARLIER ( TableName[UserID] )
        ),
        TableName[DateTime],
        ,
        asc
    )
)

Now the TimeSinceLastSave

Time since last save =
VAR Previoustime =
    CALCULATE (
        VALUES ( TableName[DateTime] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[UserID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( TableName[Index] ) )
            && TableName[EventType] = "Save",
        DATEDIFF ( Previoustime, TableName[DateTime], MINUTE )
    )

Zubair_Muhammad

Community Champion

8 years ago

Hi jpurcell

These Calculated Columns will also give you desired results

First an Index Column

Index =
IF (
    OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ),
    RANKX (
        FILTER (
            ALL ( TableName ),
            OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" )
                && TableName[UserID] = EARLIER ( TableName[UserID] )
        ),
        TableName[DateTime],
        ,
        asc
    )
)

Now the TimeSinceLastSave

Time since last save =
VAR Previoustime =
    CALCULATE (
        VALUES ( TableName[DateTime] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[UserID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( TableName[Index] ) )
            && TableName[EventType] = "Save",
        DATEDIFF ( Previoustime, TableName[DateTime], MINUTE )
    )