Reference previous row based on multiple criteria

[scottbell]

Hi All,

I've been trying to create a caluclated column based on multipul critera for my event table.

I have an event table with features such as Event ID,Customer ID,Event Name, Event Type, Time etc.

I'm trying to reference the previous event to the current event as an extra column called Previous Event which is dependant upon two conditions/columns.  Is it the same Customer ID, if so then is it the next in Event after the last Event ID. What Column I want to display is Event Name or Event Type for that previous event if it exists. 

The Tables Primary Key is Event ID hence ID -1 one could give me a different customers Event. 

What I've done so far:

• I've looked at this but couldn't get my head around it exactly for my use case. I tried todo something similar by modifying it but it just isn't the right answer i think.

• I also naively tried something like this :

Previous Row = FILTER(Event, Event[Event ID] = VALUE(Event[Event ID]) - 1)

Thanks in Advance!

Scott

[v-yulgu-msft · Accepted solution]

Hi @scottbell,

Suppose your data table is like:

Please try below formula to get previous event.

Previous Event =
CALCULATE (
    LASTNONBLANK ( 'Event Table'[Event Name], 1 ),
    FILTER (
        'Event Table',
        'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] )
            && 'Event Table'[Event ID]
                = EARLIER ( 'Event Table'[Event ID] ) - 1
    )
)

Best regards,
Yuliana Gu

[Anonymous]

https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298

Please help me out on this

---

@v-yulgu-msft wrote:

Hi @scottbell,

Suppose your data table is like:

Please try below formula to get previous event.

Previous Event =
CALCULATE (
    LASTNONBLANK ( 'Event Table'[Event Name], 1 ),
    FILTER (
        'Event Table',
        'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] )
            && 'Event Table'[Event ID]
                = EARLIER ( 'Event Table'[Event ID] ) - 1
    )
)

Best regards,
Yuliana Gu

---

---

@v-yulgu-msft wrote:

Hi @scottbell,

Suppose your data table is like:

Please try below formula to get previous event.

Previous Event =
CALCULATE (
    LASTNONBLANK ( 'Event Table'[Event Name], 1 ),
    FILTER (
        'Event Table',
        'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] )
            && 'Event Table'[Event ID]
                = EARLIER ( 'Event Table'[Event ID] ) - 1
    )
)

Best regards,
Yuliana Gu

---

[scottbell]

Thanks Yuliana,

Changing the column names and it works perfectly 🙂

I'm assuming I can add extra conditions like this? Because Customers may belong to multipul Orgs and be using it across their systems so I want to limit to their Org context.

  FILTER (
        'Event Table',
        'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] )
            && 'Event Table'[Event ID]
                = EARLIER ( 'Event Table'[Event ID] ) - 1
             &&'Event Table'[Org ID] = EARLIER('Event Table'[Org ID]) -1
    )