Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
david_h
Regular Visitor

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 within each "ID"

 

I can pull the dates in using dates between function, but I am having trouble filtering the table properly. I tried earlier so I don't have multiple values and it only calculates the cumulative return within a each "ID"

 

I would prefer a calculated column as it's easier to then use a measure to calculate hit rate and other success measures.

 

5dfwd rtn = PRODUCTX(filter('table1',datesbetween(

'table1'[Date],'table1'[First Date],'table1'[last date]) && 'table1'[ID]=earlier('table1'[ID],1) && 'table1'[Asset Pair]=earlier('table1'[Asset Pair],1),),
(1+'table1'[Asset Rtn])/100)

 

Asset PairDateSignal NameIDAsset ReturnFirst DateLast Date
Pair 131/1/2020signal 1Pair 1 signal 1-0.131/1/20205/2/2020
Pair 130/1/2020signal 1Pair 1 signal 1430/1/20204/2/2020
Pair 129/1/2020signal 1Pair 1 signal 14.729/1/20203/2/2020
Pair 128/1/2020signal 1Pair 1 signal 13.228/1/20202/2/2020
Pair 127/1/2020signal 1Pair 1 signal 12.127/1/20201/2/2020
Pair 231/1/2020signal 1Pair 2 signal 1-0.131/1/20205/2/2020
Pair 230/1/2020signal 1Pair 2 signal 1430/1/20204/2/2020
Pair 229/1/2020signal 1Pair 2 signal 14.729/1/20203/2/2020
Pair 228/1/2020signal 1Pair 2 signal 13.228/1/20202/2/2020
Pair 227/1/2020signal 1Pair 2 signal 12.127/1/20201/2/2020

 

many thanks

1 ACCEPTED SOLUTION

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)
)

 

5.jpg

 

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)
)

 

6.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @david_h ,

 

We can use the following measure to meet your requirement:

 

 

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
)

 

 

 


If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

example data.JPG

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)
)

 

5.jpg

 

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)
)

 

6.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1.146175 -1 *100 = 14.62%

 

I'm just converting the geometric return series into a standard % return.

 

The formula certainly works for me so thanks for your efforts, but due to calculation time, I've decided to alter the source data to provide the 5d returns instead and just import to powerbi.

 

thanks again.

@david_h  if Product over next 5 days is your desired output, how are you arriving there from 1+(assetreturn/100).

 

What is the calculation that you are applying?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.