cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpc_development Regular Visitor
Regular Visitor

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 3
Community Support Team
Community Support Team

Re: Need help optimising / simplifying formula and additional filtering

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 Regular Visitor
Regular Visitor

Re: Need help optimising / simplifying formula and additional filtering

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

Community Support Team
Community Support Team

Re: Need help optimising / simplifying formula and additional filtering

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,984)