How to get value from previous row in current calculated column

[Anonymous]

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 · Accepted solution]

Hi @Anonymous 
If B is a calculated column then

B =
SUMX (
    FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
    'Table'[A]
)

[tamerj1 · Accepted solution]

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?

[Anonymous]

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)

[tamerj1 · Accepted solution]

@Anonymous 

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

[tamerj1 · Accepted solution]

Hi @Anonymous 
If B is a calculated column then

B =
SUMX (
    FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
    'Table'[A]
)

[Anonymous]

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

[tamerj1 · Accepted solution]

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?

[Anonymous]

Amazing, and thank you so much for solving my issue! ❤️

[tamerj1]

@Anonymous 

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