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

hussainasfia1's avatar
hussainasfia1
New Member
2 years ago

Hello there,

            I have a similar issue where I have several colmns but I just need time difference from the same colmn/EventDt time based of different colmn that has OrderStatus. Kindly help!! I am really new to power BI, dont use DAX a lot.My table looks like below.

Order ID/ EventDt Time/ OrderStatus. each is a colmn.

for a single order at different timings the status changed from recieved to filled to packaged to shipped. I need the average and also the time difference. Please feel free to ask any questions for clarification.