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