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

)

Solution 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.

Solution 2

An alternative to Petr's elegant solution is to approach this using a measure.

Which is best in this case will depend on the specifics of your model, the size of your table, how often you will use this calculation and how many times you will want to replicate it (i.e. if you wanted to add 10 calc columns, that would be a bad idea).

I like the measure approach because it uses a very re-usable pattern:

=
SUMX (
    VALUES ( Progress[ID] ),
    CALCULATE (
        SUM ( Progress[PERCENTAGE] ),
        FILTER (
            ALL ( Progress ),
            Progress[ID]
                = MAX ( Progress[ID] ) - 1
        )
    )
)

Be careful of totals using this method - turning them off is probably the 'right' thing to do.

Formula prettified using Dax Formatter

Comments

[JShmay]

Thanks for the help, I've edited my post to help explain my point of view.

[Petr Havlik]

never thought about using sumx - values - calculate - filter in one formula. that's just insane (and genius) :)

[Jacob]

Cheers Peter! That's the beauty of DAX - will be a while before any of us stop having these moments!