PrevRow + index problem

[Anonymous]

Guys, i'm using dax to get my prev row value based on my index number. 
First, let's have a look at my table

Ok, i need to sort first by my ID number to group all values that are the same, then i need to sort by date/time, to get the order of how our process was done. 

Then i created my index to get my prev value using a var. 

The problem is, when my BI are updated, a new item enter as my last index and doesnt group with the other id numbers. 
And this new item will not be counted in my process because it dont fill the requirementes of my if. 

This is how i got my prev row "

Acionamento =

var idx = [Index]

var id_num = [ID Number]

return

CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = idx-1 )))"

So, i have no idea how to fix it. 

[v-yingjl · Accepted solution]

Hello @Vieiraguilherme ,

You can create this computed column instead of the index column:

Sort =
VAR _rank =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[ID Number] = EARLIER ( 'Table'[ID Number] ) ),
        'Table'[Changed On Timestamp],
        ,
        ASC,
        DENSE
    )
VAR _sort =
    IF (
        [ID Number] = MIN ( 'Table'[ID Number] ),
        _rank,
        _rank
            + CALCULATE (
                COUNTROWS (
                    FILTER ( ALL ( 'Table' ), 'Table'[ID Number] < MAX ( 'Table'[ID Number] ) )
                )
            )
    )
RETURN
    _sort

Next, create a measure to get the previous row:

_prerow = 
CALCULATE (
    MAX ( 'Table'[Changed On Timestamp] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Sort]
            = CALCULATE (
                MAX ( 'Table'[Sort] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Sort] < MAX ( 'Table'[Sort] ) )
            )
    )
)

Current Result:

When you add a new row to the table, the value of the previous row is still received successfully:

Attached a sample file in the next one, hopes to help you.

Best Looks,
Yingjie Li

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, do you thing this collumn/measure will work with a BI that have 250.000 rows? 

[v-yingjl]

Hi @Anonymous ,

The calculated column and measure both should work.

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

[amitchandak]

@Anonymous , Try like

new measure

Acionamento =
CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = max( 'table'[Index] )-1))

new column

Acionamento =
CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = earlier( 'table'[Index] )-1))