Find rows relative to current row's value in excel (DAX)

Question

Is there a way to filter rows based on your current row's value using DAX (I'm using power pivot) ?

In other words, If I had a table "progress" with 'ID' that is incremented in each row, and a 'Percentage' columns and another table containing

I want to create a column called old percentage = percentage of (progress[ID] -1)

Is this possible in excel ?

I couldn't find any straightforward command in this, and excuse me if there were, I am still new to power pivot.

My way of doing it is perhaps to create a new column old ID = progress[ID] -1 then create a new table in power pivot which is a duplicate of the current table but then I link it with Old ID instead of the current ID. Then in the end I do a old percentage column = RELATED([percentage]);

Is this a valid approach towards the problem ? And can this be further optimized ?

Thank you.

EDIT: I've added an image to help display what I need

)

Answer 1

JShmay,

the solution could be much simpler if you use combination of SUMX function and EARLIER.

Simply add a new calculated column with this formula:

=SUMX (
    FILTER ( Progress, EARLIER ( [ID] ) = [ID] + 1 ),
    Progress[PERCENTAGE]
)

EARLIER returns the current row, so if you compare it with the previous one, it can then return the correct value. This approach might not be very intuitive, but it's much more efficient and could save you lot of time.

The output should then look like this:

Try playing around to achieve the desired result, but I believe this is the simplest and most efficient approach (as far as processing time goes).

Hope this helps.

UPDATE: Read this article about using EARLIER in DAX queries. It might be helpful.