Need help optimising / simplifying formula and additional filtering

[dpc_development]

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.

[v-xicai]

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]

@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]

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.