DAX - 引用自身的公式 [英] DAX - formula referencing itself

问题描述

我正在努力在 DAX 中重新创建以下 Excel 逻辑:

Cont 和 CF 都是数据列(来自 SQL 数据库)，而 A 值是动态的，因为它来自 What-if分析:

正如您在屏幕截图中看到的那样，A 度量值无法正确计算年份 > 2021 的值.我根本无法理解公式如何引用自身"(即前一行的结果).我尝试使用 EARLIER 功能，但它似乎不适用于措施.我还尝试创建一个计算列而不是一个度量，但这些列无法与假设参数配合.

这是我目前所拥有的:

<代码>mA =var Cont = SELECTEDVALUE(JP[Cont])var CF = SELECTEDVALUE(JP[CF])变量 AR = 1.03返回A[参数值] * AR - CF + Cont

任何提示和解决方案将不胜感激.感谢您的宝贵时间.

解决方案

不能递归地自引用 DAX 中的列.

请参阅此相关问题:

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

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.

这篇关于DAX - 引用自身的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！