I am struggling to recreate the following Excel logic in DAX:
Cont
and CF
are both data columns (sourced from SQL database), while A
value is dynamic as it comes from What-if analysis:
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.
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
In DAX this can be written as follows (where
Yr = N
andJP[Year] = i
):If you're starting with 2021, then you'll need to subtract 2020 from the
Yr
variable.