Forum Discussion

scottbell's avatar
scottbell
Regular Visitor
8 years ago
Solved

Reference previous row based on multiple criteria

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, T...
Need Help
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
scottbell
Regular Visitor
8 years ago

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
    )