Need help with iterative measure

[dpc_development]

I have a model with three columns - list_id, sequence, daily_return.

Basically, for each list_id, there is a sequence of numbers from 0 onwards (0, 1, 2, ..., X) with different percentage values in the daily returns column.

In my chart, I am trying to calculate a measure 'cumulative_return' as follows:

cumulative_return = previous_cumulative_return + (previous_cumulative_return * daily_return)

The starting cumulative_return is always one.

Hence, I'd need an iterative sum that factors into the previous calculated metric value. I have the following so far, but am not sure how to multiply daily_return with the previous cumulative_return value. I also tried to create another calculated measure with sequence - 1, but that didn't go anywhere.

cumulative_return = SUMX(
    FILTER(
        FILTER(ALLSELECTED('CMC Daily Return'), 'CMC Daily Return'[list_id] = MAX('CMC Daily Return'[list_id])),
        'CMC Daily Return'[sequence] <= MAX('CMC Daily Return'[sequence])
    ),
    IF('CMC Daily Return'[sequence] = MIN('CMC Daily Return'[sequence]), 1,
        AVERAGE('CMC Daily Return'[daily_return]) )
)

Appreciate any inputs you may have. Perhaps I can somehow use Exponential Sum and Natural Logs.

[OwenAuger]

@dpc_development 

In this case, where you have returns compounding by a sequence of rates, you can use PRODUCTX.

Stating your formula another way:

cumulative_return = previous_cumulative_return * (1 + daily_return)

cumulative_return =
VAR max_list_id =
    MAX ( 'CMC Daily Return'[list_id] )
VAR max_sequence =
    MAX ( 'CMC Daily Return'[sequence] )
RETURN
    CALCULATE (
        PRODUCTX ( 'CMC Daily Returns', 1 + 'CMC Daily Return'[daily_return] ),
        'CMC Daily Return'[list_id] = max_list_id,
        'CMC Daily Return'[sequence] <= max_sequence,
        ALLSELECTED ( 'CMC Daily Return' )
    )

I have re-organised the formula slightly.

The code in red is the actual return calcuation, i.e. the  product of (1+ return) produced by iterating over the filtered 'CMC Daily Returns' table.

The code in green is the set of filters that are applied before performing the calculation, which use max_list_id and max_sequence variables declared earlier.

Does this give the right result?

As a side note, as you suggested, you could use SUMX to add the natural logarithms of (1 + return) and then raise e to the power of this sum with EXP. This used to be the only way of doing this before the PRODUCTX function was added.

See this article for example: https://powerpivotpro.com/2013/11/cumulative-interest-or-inflation-multiplying-every-value-in-a-colu...

Regards,

Owen

[dpc_development]

@OwenAuger That works, but I also have a date column in my model. When I change the date slicer, my formula recalculates from '1', which is the intended behaviour. How would I get that in your revision. Will also try to figure it out.

EDIT

@OwenAuger It seems that when I change the date and the initial sequences 0, 1, 2 visually are removed from the table/chart, the formula still continues to do PRODUCTX  calculation on sequences 0, 1, 2. Need to adjust the formula such that only the filtered data is used to do calculations.

[OwenAuger]

Hi again @dpc_development 

Any chance you could post a link to a sanitised model exhibiting the problem? It may be easier to diagnose that way.

Is the Date column in the 'CMC Daily Return' table or are you using a separate Date table?

I created a dummy model at my end where I added a Date column to the CMC Daily Return table, and when a Date filter is applied, the above measure only compounded over sequence values for the filtered dates.

Regards,

Owen

[dpc_development]

Hi @OwenAuger , please see this file. In the current model, if you change the date slicer from 20th Feb to 21st Feb, the current_cumulative_return column shows 1.015 against 21st Feb and 0.963 against 22nd Feb. I want it to show 1.000 and 0.950 respectively.

Similarly, if you change the date to 24th Feb, then 24th and 25th should show 1.000 and 0.862. Basically, the minimum sequence should restart from 1 and PRODUCTX should calculate as if the first cumulative return in the filtered data is '1'.

Note:

1) In reality, the sequence column in my data set was called id, but in the original question I used sequence to make it easier to understand. I reverted to 'id' in the model file.

2) For the PRODUCTX part of the formula, I also tried the following IF condition to no avail.

PRODUCTX(
            'CMC Daily Return',
            1 + IF('CMC Daily Return'[id] = min_id, 0, 'CMC Daily Return'[daily_return])
        )

In either case, it is returning what your original formula returned, but it is delayed by one row.

Thank you for your continued help with this.

[OwenAuger · Accepted solution]

EDIT: Posted the below before seeing your post. Looks like you have a solution already.

Thanks for that 🙂

So you basically want to replace the return with zero for the first id in the context of the overall filters, but thereafter use the return from the table. You were on the right track with the code snippet you just posted, but I have defined min_id_allselected which gives the min_id for the current list (i.e. max_list).

This measure worked for me. There are potentially different ways you could write this but it should do the trick:

current_cumulative_return = 
VAR max_list =
    MAX ( 'CMC Daily Return'[list_id] )
VAR max_id =
    MAX ( 'CMC Daily Return'[id] )
VAR min_id_allselected =
    CALCULATE (
        MIN ( 'CMC Daily Return'[id] ),
        'CMC Daily Return'[list_id] = max_list,
        ALLSELECTED ( 'CMC Daily Return' )
    )
RETURN
    CALCULATE (
        PRODUCTX (
            'CMC Daily Return',
            1 + IF ( 'CMC Daily Return'[id] = min_id_allselected, 0, 'CMC Daily Return'[daily_return] )
        ),
        'CMC Daily Return'[list_id] = max_list,
        'CMC Daily Return'[id] <= max_id,
        ALLSELECTED ( 'CMC Daily Return' )
    )

Regards,

Owen

[dpc_development]

Haha! You posted your final one measure solution before I was finally able to figure it out. Thanks for helping me get there.

[dpc_development]

Hi @OwenAuger, can you please help me with another question?

https://community.powerbi.com/t5/Desktop/Calculate-average-of-calculated-measure-across-all-dimensio...

[dpc_development]

EDIT: Originally I had the below split into three measures, but now I was able to get the formula that I need. Thanks for your help.

cumulative_return = 
VAR list = MAX('CMC Daily Return'[list_id])
VAR seq = MAX('CMC Daily Return'[id])
VAR min_seq = CALCULATE(MIN('CMC Daily Return'[id]), 'CMC Daily Return'[list_id] = list, ALLSELECTED('CMC Daily Return'))
RETURN
    CALCULATE (
        PRODUCTX( 'CMC Daily Return',
                    1 + IF('CMC Daily Return'[id] > min_seq, 'CMC Daily Return'[daily_return], 0) 
        ),
        'CMC Daily Return'[list_id] = list,
        'CMC Daily Return'[id] <= seq,
        ALLSELECTED('CMC Daily Return')
    )