Forum Discussion

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

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's avatar
OwenAuger
Icon for Super User rankSuper User
7 years ago

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