Forum Discussion

jpurcell's avatar
jpurcell
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's avatar
    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's avatar
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 )
    )

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi all

I tried this formula on my situation because it looks like the same goal, but when adapting the formula it only works partly. The strange thing is that once in a while it actually calculates the time between the events. (in the table shown below It only calculated the last event) .

 

My situation is show below. I want to calculate the time between when an event occurs ‘’Gekomen’’ and when the events disappears ‘’Gegaan’’, shown in the Status row. The Events have an ID which is connected by an type of event, so it can occur multiple times, I want to know the time between the status (gegaan & gekomen). (the difficult part is that all the values are appearing trough each other and  the events can appear and disappear at the same time, also the events can happen multiple times in a short time.

 I hope you can help me!

 

Status                   Date and Time                   ID           Index    Duration

Gegaan                1-2-2021 09:34                  10           7            

Gegaan                1-2-2021 09:34                  637         7            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:35                  637         9            

Gegaan                1-2-2021 09:35                  10           9            

Gegaan                1-2-2021 09:37                  635         1            

Gegaan                1-2-2021 09:37                  626         3            

Gekomen             1-2-2021 09:37                  635         1            

Gekomen             1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  626         3            

Gekomen              1-2-2021 09:37                  655         1            

Gegaan                 1-2-2021 09:37                  655         1            

Gekomen              1-2-2021 09:38                  656         1            

Gegaan                 1-2-2021 09:39                  656         2             1