powerbidaxtabularssas-tabular

DAX expression for cumulative returns


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

enter image description here


Solution

  • 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.