Forum Discussion
Need help with iterative measure
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.
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
8 Replies
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-column-why-dont-we-have-productx/
Regards,
Owen
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.
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
