Calculation based on previous calculated value

[Barry_Sophus]

I've been trying to create a column with a calculated value based on the previous calculated value but can't figure out how.

This is what I have so far:

var val = 1.5

var A = cos(val)*2

return A

Column A	Result
=COS(1.5)*2	0.141474403335406
=COS(A1)*2	1.98001835431133
=COS(A2)*2	-0.795791425329509
=COS(A3)*2	1.39943915409634
=COS(A4)*2	0.341039603168344
=COS(A5)*2	1.88481492380106
=COS(A6)*2	-0.617766415528922
=COS(A7)*2	1.63034843303413
=COS(A8)*2	-0.119033825371811
=COS(A9)*2	1.98584767068623
=COS(A10)*2	-0.806473830974154
=COS(A11)*2	1.38409620019485
=COS(A12)*2	0.371234765893159
=COS(A13)*2	1.8637602491656

[OwenAuger · Accepted solution]

Hi @Barry_Sophus 

This is difficult to handle with DAX, since DAX doesn't do recursion 😞

(This may seem silly because, in Excel, we can easily copy a formula and make use of relative references.)

With DAX, we could only produce these values in a calculated column if we could come up with a closed-form DAX expression, and as far as I can tell there is no such closed-form expression that produces terms solving the recurrence relation

a(n) = 2 * cos( a(n-1) )

but I would be happy to be proven wrong (see WolframAlpha ).

If this just needs to be a static column, you can instead use Power Query or some other process upstream to produce the values.

I have attached a small example using List.Generate in Power Query. This query relies on parameters StartingValue (1.5) and MaxIndex (14).

let
    #"Generate Values" = 
      List.Generate(
          ()=> [Index = 0, Value = StartingValue],
          each [Index] <= MaxIndex,
          each [Index = [Index]+1, Value = 2*Number.Cos([Value])]
      ),
    #"Convert to Table" = Table.FromRecords(#"Generate Values", type table[Index = Int64.Type, Value = number])
in
    #"Convert to Table"

Does this help?

Regards