英文:
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]))
英文:
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]<=MAX(EffectiveReturnsF[Date]))
答案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] >= FundDate ) &&
( EffectiveReturnsF[Date] <= CurrentDate )
)
RETURN EffectiveReturnsF[Funds] * CumulativeReturnRate
),
REMOVEFILTERS ( EffectiveReturnsF ),
EffectiveReturnsF[Date] <= 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论