DAX - formula referencing itself

2
votes

I am struggling to recreate the following Excel logic in DAX:

enter image description here

Cont and CF are both data columns (sourced from SQL database), while A value is dynamic as it comes from What-if analysis:

enter image description here

As you can see on the screenshot, A measure doesn't properly calculate the values for year > 2021. I simply fail to understand how the formula can reference "itself" (i.e. previous row's result). I tried to play with EARLIER function but it doesn't seem to work with measures. I also tried to create a calculated column instead of a measure, but these fail to cooperate with what-if parameters.

Here's what I have so far:

mA = 
    var Cont = SELECTEDVALUE(JP[Cont])
    var CF = SELECTEDVALUE(JP[CF])
    var AR =  1.03
return
    A[Parameter Value] * AR - CF + Cont

Any tips & solutions would be much appreciated. Thank you for your time.

1
powerbidax

1 Answers

3
votes

You cannot recursively self-reference a column in DAX.

See this related question: Recursion in DAX

However, for this particular case, you can create a closed-form formula for the column you want by realizing that for year N, the result you want can be written as

A * AR^N + sum_(i=1)^N AR^(N-i) (Cont_i - CF_i)

In DAX this can be written as follows (where Yr = N and JP[Year] = i):

mA = 
VAR AR = 1.03
VAR Yr =  SELECTEDVALUE ( JP[Year] )
VAR Temp =
    ADDCOLUMNS (
        FILTER ( ALL ( JP ), JP[Year] <= Yr ),
        "Const", ( JP[Cont] - JP[CF] ) * POWER ( AR, Yr - JP[Year] )
    )
RETURN
    A[Parameter Value] * POWER ( AR, Yr ) + SUMX ( Temp, [Const] )

If you're starting with 2021, then you'll need to subtract 2020 from the Yr variable.

Result