topic Re: How to get value from previous row in current calculated column in DAX Commands and Tips

How to get value from previous row in current calculated column

Anonymous — Wed, 29 Mar 2023 11:16:55 GMT

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

Re: How to get value from previous row in current calculated column

tamerj1 — Wed, 29 Mar 2023 11:31:14 GMT

Hi Anonymous
If B is a calculated column then

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

Re: How to get value from previous row in current calculated column

Anonymous — Wed, 29 Mar 2023 11:45:28 GMT

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

Re: How to get value from previous row in current calculated column

tamerj1 — Wed, 29 Mar 2023 12:02:04 GMT

Anonymous

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

Re: How to get value from previous row in current calculated column

tamerj1 — Thu, 30 Mar 2023 04:16:01 GMT

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?

Re: How to get value from previous row in current calculated column

Anonymous — Thu, 30 Mar 2023 07:32:38 GMT

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

Re: How to get value from previous row in current calculated column

Anonymous — Wed, 10 May 2023 12:27:11 GMT

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)

Re: How to get value from previous row in current calculated column

tamerj1 — Wed, 10 May 2023 12:49:18 GMT

Anonymous

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