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

 

  • 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?

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 
    If B is a calculated column then

    B =
    SUMX (
        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
        'Table'[A]
    )
    • Anonymous's avatar
      Anonymous
      Not applicable

      That did work fine. What if I want to expand the complexity by the following, knowing that B1 = 1, 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        Anonymous 

        Ya that is pretty complex. I'll have a look at it tomorrow. 

  • Anonymous's avatar
    Anonymous
    Not applicable

    I have another similar issue. 
    Initial value in column C (C1) = 100
    The following values in column C is:  C_i (C_i where i > 1) =  C_i-1 *(1+A_i)