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]))
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.