Forum Discussion

Regular Visitor

6 years ago

Solved

cumulative 5 day return (productx)

Struggling with this for a while. Tried many iterations of Productx and filters. all i want is a calculated column to the right that reflects the 5 day forward Asset return on a cumulative basis ...

Need Help

v-lid-msft

6 years ago

Hi david_h ,

Sorry for that we put the incorrect formula and screenshot in our previous reply, But we are still confused about how to get the result such as 14.62. If we product of these numbers across the next 5 days, it should be 1.021*1.032*1.047*1.04*0.999=1.146175, such as using calculated column:

5dfwd rtn = 
PRODUCTX (
    FILTER (
        'table1',
        'table1'[Date] >= EARLIER('table1'[First Date]) && 'table1'[Date] <= EARLIER('table1'[last date] )
            && 'table1'[ID] = EARLIER ( 'table1'[ID] )
            && 'table1'[Asset Pair] = EARLIER ( 'table1'[Asset Pair] )
    ),
    ( 1 + 'table1'[Asset Return]  / 100)
)

We can also use the following measure:

Measure = 
PRODUCTX (
    FILTER (
        ALLSELECTED('table1'),
        'table1'[Date] >= min('table1'[First Date]) && 'table1'[Date] <= max('table1'[last date] )
            && 'table1'[ID] in FILTERS(  'table1'[ID] )
            && 'table1'[Asset Pair]  in FILTERS( 'table1'[Asset Pair] )
    ),
    ( 1 + 'table1'[Asset Return]  / 100)
)

Best regards,

cumulative-5-day-return-productx.pbix78 KB

david_h

Regular Visitor

6 years ago

tried your formula and have a few problems

1.) I have 3.6m lines of data so it takes a long time to calculate (hours)

2.) I'm not getting the answer I want really

I would expect the answer to be below (see final columns to the right)

Within each unique ID I would expect the 5 day forward return.

This is calculated by taking the (daily return /100)+1, and then the product of these numbers across the next 5 days.

I then thought about maybe it's better to use a measure as I can apply date filter/and other filters to it but I'm having issues with it finding the data there via the earlier functions it doesn't seem able to look up the correct rows (there's a date filter applied to this measure)

Below is the output I would expect on the far right thanks again.