Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
scottbell
Regular Visitor

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

 

 

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @scottbell,

 

Suppose your data table is like:

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @scottbell,

 

Suppose your data table is like:

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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:

1.PNG

 

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:

1.PNG

 

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


 

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
    )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.