Pulling the previous row from a table stored as a variable in DAX

[A_Hale_]

Has anybody come across the issue of returning the previous row's value from a table stored as a variable in DAX? In other words I am trying to return a new table with all of the rows of a referenced variable table (Columns 1 & 2) and an aditional column with the previous row's value (Column 3).

[Anonymous · Accepted solution]

Hi @A_Hale_ ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table 2 =
ADDCOLUMNS (
    'Table',
    "Previous Rows Value",
        CALCULATE (
            MAX ( 'Table'[Values] ),
            FILTER ( 'Table', 'Table'[Rank] = EARLIER ( 'Table'[Rank] ) - 1 )
        )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Ahmedx · Accepted solution]

Can be done without creating a new table
Sample PBIX file attached
https://dropmefiles.com/Id5SI

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous · Accepted solution]

Hi @A_Hale_ ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table 2 =
ADDCOLUMNS (
    'Table',
    "Previous Rows Value",
        CALCULATE (
            MAX ( 'Table'[Values] ),
            FILTER ( 'Table', 'Table'[Rank] = EARLIER ( 'Table'[Rank] ) - 1 )
        )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[vicky_]

I can't say I have any experience with these functions myself, but I think the INDEX and OFFSET functions would give you what you want, depending on the exact calculation.