Forum Discussion

dpc_development's avatar
dpc_development
Helper III
6 years ago

Need help optimising / simplifying formula and additional filtering

I have a dataset with variour portfolios, each made up of various lists with their ratios. The dataset has daily return values, and I am calculating cumulative return as follows:

cumulative_return = previous cumulative_return * (1 + daily_return)

 

To achieve that I am using PRODUCTX, multiplying the PRODUCTX output with the ratio, and summing all the components with SUMX.

 

The following is my current formula:

cr3 = 
var tbl = ALLSELECTED('CMC Daily Return')
var onDate = MAX('CMC Daily Return'[last_updated])
var fk_count = DISTINCTCOUNT('CMC Daily Return'[fk])
RETURN
SUMX(
    'CMC Daily Return',

    var fk_item = 'CMC Daily Return'[fk]
    var list_item = 'CMC Daily Return'[list_id]
    var fromDate = CALCULATE(
        MIN('CMC Daily Return'[last_updated]),
        tbl,
        'CMC Daily Return'[fk] = fk_item,
        'CMC Daily Return'[list_id] = list_item
    )
    RETURN
    CALCULATE(
        PRODUCTX(
            'CMC Daily Return',
            IF('CMC Daily Return'[last_updated] = fromDate, 1, 1 + 'CMC Daily Return'[daily_return])
        ),
        tbl,
        'CMC Daily Return'[fk] = fk_item,
        'CMC Daily Return'[list_id] = list_item,
        'CMC Daily Return'[last_updated] >= fromDate,
        'CMC Daily Return'[last_updated] <= onDate
    ) * 'CMC Daily Return'[ratio]
) / fk_count

 

The above formula works, but I feel it could be simplified or optimised better. I'd also like to add another metric yesterdays_cumulative_return, where I'd just do a calculate of cumulative_return with a filtered dataset until yesterday.

 

Currently that is not working with the above formula. Appreciate any help.

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support

    Hi dpc_development ,

     

    You may change the formula above like DAX below.

     

    cr3 =
    VAR tbl =
        ALLSELECTED ( 'CMC Daily Return' )
    VAR onDate =
        MAX ( 'CMC Daily Return'[last_updated] )
    VAR fk_count =
        DISTINCTCOUNT ( 'CMC Daily Return'[fk] )
    VAR fk_item =
        MAX ( 'CMC Daily Return'[fk] )
    VAR list_item =
        MAX ( 'CMC Daily Return'[list_id] )
    VAR fromDate =
        CALCULATE (
            MIN ( 'CMC Daily Return'[last_updated] ),
            FILTER (
                tbl,
                'CMC Daily Return'[fk] = fk_item
                    && 'CMC Daily Return'[list_id] = list_item
            )
        )
    VAR d =
        IF (
            'CMC Daily Return'[last_updated] = fromDate,
            1,
            1 + 'CMC Daily Return'[daily_return]
        )
    VAR _return =
        CALCULATE (
            PRODUCTX ( 'CMC Daily Return', d ),
            FILTER (
                tbl,
                'CMC Daily Return'[fk] = fk_item
                    && 'CMC Daily Return'[list_id] = list_item
                    && 'CMC Daily Return'[last_updated] >= fromDate
                    && 'CMC Daily Return'[last_updated] <= onDate
            )
        ) * 'CMC Daily Return'[ratio]
    RETURN
        SUMX ( 'CMC Daily Return', _return ) / fk_count

     

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • dpc_development's avatar
      dpc_development
      Helper III

      v-xicaiThat will not work, since I have multiple list_items under each fk_item. Hence, putting the assignment of list_item and minDate at the beginning will only make the formula calculate the values for one list_item.

       

      I have extracted the dataset and formula into a separate modeling file, but was not sure how to attach it here.

      • v-xicai's avatar
        v-xicai
        Community Support

        Hi dpc_development ,

         

        I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

         

        Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

         

        Best Regards,

        Amy

         

        Community Support Team _ Amy

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.