DAX 表达式用于累计回报

huangapple go评论65阅读模式
英文:

DAX expression for cumulative returns

问题

我需要找到累积回报,如图所示。以下是输入表格:

日期 基金 回报
45047 100 0.1
45048 100 -0.05
45049 200 0.2
45050 200 0.1
45051 100 -0.05

我需要 "Cumulative Returns" 的 DAX 表达式为 804.97,以及 "Effective Returns" 为 15%。我尝试了多种方法,但未能得到正确答案。

我正在使用的示例 DAX 表达式是:

EffectiveReturnsRE = CALCULATE(SUMX(EffectiveReturnsF
                                ,EffectiveReturnsF[Returns] * EffectiveReturnsF[Funds])
                                ,ALL(EffectiveReturnsF)
                                ,EffectiveReturnsF[Date]<=MAX(EffectiveReturnsF[Date]))

DAX 表达式用于累计回报

英文:

I have to find cumulative returns as show in the image. below is the input table

Date Funds Returns
45047 100 0.1
45048 100 -0.05
45049 200 0.2
45050 200 0.1
45051 100 -0.05

I need DAX expression for "Cumulative Returns" 804.97 and "Effective Returns" 15%. I tried many ways but couldn't get right answer.

A sample DAX I am working on

EffectiveReturnsRE = CALCULATE(SUMX(EffectiveReturnsF
                                ,EffectiveReturnsF[Returns] * EffectiveReturnsF[Funds])
                                ,ALL(EffectiveReturnsF)
                                ,EffectiveReturnsF[Date]&lt;=MAX(EffectiveReturnsF[Date]))

DAX 表达式用于累计回报

答案1

得分: 1

简短回答: 这是一个累积回报测量的示例DAX计算。有效回报只需将此值除以基金的累积总和(请尝试一下)。

Cumulative Returns = 

VAR CurrentDate = MAX ( EffectiveReturnsF[Date] )
RETURN
CALCULATE (
    SUMX (
        EffectiveReturnsF,
        VAR FundDate = EffectiveReturnsF[Date]
        VAR CumulativeReturnRate =
            CALCULATE (
                PRODUCTX (
                    EffectiveReturnsF,
                    1 + EffectiveReturnsF[Returns]
                ),
                REMOVEFILTERS ( EffectiveReturnsF ),
                ( EffectiveReturnsF[Date] >= FundDate ) &&
                    ( EffectiveReturnsF[Date] <= CurrentDate )
            )
        RETURN EffectiveReturnsF[Funds] * CumulativeReturnRate
    ),
    REMOVEFILTERS ( EffectiveReturnsF ),
    EffectiveReturnsF[Date] <= CurrentDate
)

原因: 这个问题的棘手之处(也是我觉得有趣的地方 :)) 在于累积回报的计算是“递归”的。自然而然地,我们可以将累积回报的定义循环地表示如下(请注意,它包括了自身的“累积回报”):

累积回报(今天)= 累积基金(今天)×(100% + 回报(今天))

累积基金(今天)= 累积回报(昨天)+ 基金(今天)

在一般的编程语言如C和Python中,通过使用递归函数来计算这个是很直接的。然而,DAX无法解决递归计算

解决这个问题的方法是将递归计算逻辑扩展为“迭代”逻辑。DAX擅长迭代计算

让我们分别考虑每个日期的基金。例如,第1天的100基金在第5天变成了131.043(= 100 × 110% × 95% × 120% × 110% × 95%)。第3天的200基金在第5天变成了250.8(= 200 × 120% × 110% × 95%)。

总回报可以计算为分别计算每个日期的累积回报之和。

通过这种方式分解问题,可以使用DAX迭代工具包——SUMX和PRODUCTX来解决它。

英文:

Short answer: Here is an example DAX calculation of the Cumulative Returns measure. Effective Returns will be just dividing this value by the running total of Funds (please have a try.)

Cumulative Returns = 

VAR CurrentDate = MAX ( EffectiveReturnsF[Date] )
RETURN
CALCULATE (
    SUMX (
        EffectiveReturnsF,
        VAR FundDate = EffectiveReturnsF[Date]
        VAR CumulativeReturnRate =
            CALCULATE (
                PRODUCTX (
                    EffectiveReturnsF,
                    1 + EffectiveReturnsF[Returns]
                ),
                REMOVEFILTERS ( EffectiveReturnsF ),
                ( EffectiveReturnsF[Date] &gt;= FundDate ) &amp;&amp;
                    ( EffectiveReturnsF[Date] &lt;= CurrentDate )
            )
        RETURN EffectiveReturnsF[Funds] * CumulativeReturnRate
    ),
    REMOVEFILTERS ( EffectiveReturnsF ),
    EffectiveReturnsF[Date] &lt;= CurrentDate
)

Reasoning: The tricky (and what I felt interesting :)) part of this question is that the
calculation of Cumulative Returns is "recursive". It is natural to think of Cumulative Returns definition cyclically as follows (please find that it includes "Cumulative Returns" in itself):

> Cumulative Returns (today) = Cumulative Funds (today) × ( 100% + Returns (today) )
>
> Cumulative Funds (today) = Cumulative Returns (yesterday) + Funds (today)

In general programming languages ​​such as C and Python, it is straightforward to compute this by using recursive functions. However, DAX cannot solve recursive calculations.

The solution for this problem is to expand the recursive calculation logic into "iterative" logic. DAX is good at iterative calculations.

Let's consider the funds for each date separately. For example, the 100 fund on Day 1 becomes 131.043 on Day 5 (= 100 × 110% × 95% × 120% × 110% × 95%). The 200 fund on Day 3 becomes 250.8 on Day 5 (= 200 × 120% × 110% × 95%).

The total return can be calculated as the sum of the cumulative returns of each date calculated separately.

By decomposing the problem this way, it becomes solvable with DAX iterator toolkits -- SUMX and PRODUCTX.

huangapple
  • 本文由 发表于 2023年5月7日 20:12:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76193850.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定