Measure that refers to its own previous value

[xxenoss]

Hi everyone, I'm trying to solve this problem,

Lets say I want to get the folowing siquence = "previos value" * 3 +2 .

So for the second row 2*3+2=8, third 8*3+2=26 and so on. 

Value = IF( MAX("Table[Index]")=1, 2, ...

Index	Value
1	2
2	8
3	26
4	80
5	242
6	728

[OwenAuger]

Hi @xxenoss 

It isn't possible for a DAX measure (or calculated column) to refer to itself, so traditional recursion isn't possible in DAX.

In these sorts of situations, I recommend doing a little algebra to convert the calculation to something that can be summed using SUMX over a cumulative set of index values, or some other closed form expression.

In your example, it turns out that your values are the sum of terms of a geometric sequence multiplied by 2.

i.e. 2*(1), 2*(1+3), 2*(1+3+3^2), 2*(1+3+3^2+3^3),... 

So after applying the formula for the sum of a geometric sequence and simplifying, you get

  Value(Index) = 3^(Index) - 1

In DAX you can write:

Value = 
VAR MaxIndex = MAX ( 'Table'[Index] )
RETURN
    POWER ( 3, MaxIndex ) - 1
 
 -- Sum of first n terms of geometric sequence =
 --    a1 * (r^n - 1 ) / ( r - 1 )
 -- We have a1 = 1 & r = 3
 -- multiplied by 2, giving
 -- 2 * (POWER ( 3, MaxIndex ) - 1) / (3 - 1 )
 -- Simplifies to expression above

You could get the same result using an iterator to sum the terms, but performance would be worse:

Value = 
VAR MaxIndex = MAX ( 'Table'[Index] )
VAR IndexValuesModified = 
    GENERATESERIES ( 0, MaxIndex - 1 )
RETURN
    2 * 
    SUMX ( 
        IndexValuesModified,
        POWER ( 3, [Value] )
    )

Regards,

Owen

[xxenoss · Accepted solution]

I found it, it is actually arithmetico geometric series. And it goes like this

It took me some time to figure it out, but it works. Thanks again for the idea. 

[v-easonf-msft]

Hi, @xxenoss 

If your problem has been solved, please accept the helpful answer as solution to close this thread.

For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[xxenoss]

Thank you so much! This worked great for this particular sequence! But is there a way to make this formula universal? Like Value = x*y+x, "Previous Value" * y+x, "Previous Value" * y+x, ...

You probably explained it here, but my math knowledgeis not sufficient to understand. 

-- Sum of first n terms of geometric sequence =
-- a1 * (r^n - 1 ) / ( r - 1 )
-- We have a1 = 1 & r = 3
-- multiplied by 2, giving
-- 2 * (POWER ( 3, MaxIndex ) - 1) / (3 - 1 )