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
Icon for Microsoft Employee rankMicrosoft 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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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