Forum Discussion

A_Hale_'s avatar
A_Hale_
Frequent Visitor
3 years ago
Solved

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

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
    Ahmedx
    3 years ago

    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.

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    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.

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.