Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to get value from previous row in current calculated column

Hi,    I am trying to replicate the following calculation in DAX, where I want to get the value from the previous row in the current column (B).   
  • tamerj1's avatar
    3 years ago

    Hi Anonymous 
    If B is a calculated column then

    B =
    SUMX (
        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
        'Table'[A]
    )
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    Please refer to attached sample file with the proposed solution

    B = 
    COALESCE (
        DIVIDE (
            1,
            PRODUCTX ( 
                FILTER ( 
                    'Table',
                    'Table'[Index] <= EARLIER ( 'Table'[Index] )
                        && 'Table'[Index] > 1 
                ),
                ( 1 + 'Table'[A] ) ^ (1/16)
            )
        ),
        1
    )

    The numbers do not 100% match yours, but when I made the my own excel sample file, the numbers were 100% matching. Could it be a rounding error?

  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    Value = 
    COALESCE (
        PRODUCTX (
            FILTER ( 
                Table2,
                Table2[Index] <= EARLIER ( Table2[Index] )
                    && Table2[Index] > 1 
            ),
            1 + Table2[A]
        ) * 100,
        100
    )