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 )
    )

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi jpurcell

 

I think this calculated table might be close

 

New Table = 


VAR SelfJoin =  
    FILTER( 
        CROSSJOIN( 
                    'Events', 
                    SELECTCOLUMNS( 
                        FILTER('Events', [EventType] in {"Save","Open"}), 
                        "xUserID",[UserID], 
                        "xDateTime",[DateTime] 
                    ) 
                ), 

                [xDateTime] < [DateTime] && 
                [UserID] = [xUserID] 
               
                ) 

VAR LastPurchases =  
    GROUPBY( 
            SelfJoin, 
            'Events'[UserID], 
            Events[DateTime], 
            "Last DateTime",MAXX( 
                                CURRENTGROUP(), 
                                [xDateTime] 
                                ) 
                        
            ) 

RETURN 
    
     ADDCOLUMNS( 
         NATURALLEFTOUTERJOIN('Events',  LastPurchases), 
         "Days since last purchase" ,  IF('Events'[EventType]="Save" , DATEDIFF([Last DateTime],[DateTime],MINUTE),BLANK())
         )
  • 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 )
    )

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

       

      That was quite ingenious .....Champ Phil_Seamark   :smileyhappy:

    • sirros_iot's avatar
      sirros_iot
      Helper III
      8 years ago

      Can I have this results only using measures?

       

    • 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

  • jpurcell's avatar
    jpurcell
    Advocate I
    8 years ago

    Thanks for the help! Both solutions worked perfectly. I loved the simplicity of the table, but I'm going to use the columns so I don't have duplicate data in my report.