Forum Discussion

dpc_development's avatar
dpc_development
Helper III
7 years ago
Solved

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 ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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's avatar
dpc_development
Helper III
7 years ago

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.

dpc_development's avatar
dpc_development
Helper III
7 years ago

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