cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnsnowlife Helper III
Helper III

Calculating Product of Instrument Contributions

I'm trying to calculate the contribution of my underlying holdings to my portfolio return. 

 

I'm struggling to get my measures to reference a value for the previous day for the specific instrument in the current row. 

 

Contributions PBIX File

 

This excel file explains what result I'm looking for. Excel

 

For each day I need:

PriceContribution = (Price % Change from previous Day)* (Instrument % of fund of previous Day)

Cumulative Contribution = ProductX(Contribution) per instrument per fund for the total date range

6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: Calculating Product of Instrument Contributions

@Johnsnowlife,

Could you please share the Excel file for me to download? I would check that what formula you use to calculate the Product of Contr+1. I guess that you create columns to calculate Pricechg, Contr and other fields in Excel, but in Power BI Desktop, you use measures instead, which give you different result.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Johnsnowlife Helper III
Helper III

Re: Calculating Product of Instrument Contributions

Hi Lydia

 

There is a link to the Excel file in the original post. In the Excel File I have used columns and a pivot table. But in the PBI file I want to use measures for eveything. 

Moderator v-yuezhe-msft
Moderator

Re: Calculating Product of Instrument Contributions

@Johnsnowlife,

I am not able to edit the Excel file and I can't view the formulas you use to create these fields in Excel. 

Since you get different values for calculated fields of Excel and measures of Power BI Desktop, I am confused about your expected result. Thus, could you please share the Excel file so that I can download rather than viewing data in Excel Online? And please check if the following measure returns your expected result in Power BI.

TotalContr = CALCULATE(productx(VALUES(DataAllFunds[Date]),1+[PriceContr]))-1

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Johnsnowlife Helper III
Helper III

Re: Calculating Product of Instrument Contributions

Try this link

Johnsnowlife Helper III
Helper III

Re: Calculating Product of Instrument Contributions

I used the below and I got my expected result for the product of the instrument contribution over my date period and calculated only for the last date. 

CumInstrContr = 
VAR TDate =
    CALCULATE ( MAX ( DataAllFunds[Date] ), ALL ( DataAllFunds ) )
RETURN
    IF (
        MAX(DataAllFunds[Date] ) = TDate,
 CALCULATE(productx(values(DataAllFunds[Date]),1+[PriceContr]),ALLSELECTED(DataAllFunds[Date]))-1,blank())

I don't really understand how the "values(DataAllFunds[Date]" part works though. Can you explain please?

 

Now I also want the sum of all the instrument price contributions per fund per day (FundDayContr). And then the product of (1+ FundDayContr) over all days in the period. 

Highlighted
Johnsnowlife Helper III
Helper III

Re: Calculating Product of Instrument Contributions

For anyone interested in this, the following formulas ended up working. However I ran into trouble with these formulas using extensive RAM and I couldn't run them for periods more than 6 months on my actual table. 

 

Can anyone advise on how I can optimise these? 

 

InstrPriceContr = 
SUMX ( DataAllFunds, [PriceChg] * [InstrPctPrevDay] )

CumInstrContr = 
VAR startDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ('Calendar'[Date] ) )
RETURN
    if(min(DataAllFunds[Date])<startDate,blank(),CALCULATE (
        PRODUCTX ( DataAllFunds, 1 + [InstrPriceContr] ),
        ALLEXCEPT ( DataAllFunds, DataAllFunds[Fund], DataAllFunds[InstrCode] ),
        DataAllFunds[Date] >= startDate
    )
        - 1
)

ProductT = 
VAR startDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ('Calendar'[Date] ) )
VAR DateT = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
RETURN
    if(max(DataAllFunds[Date])<DateT,blank(),CALCULATE(PRODUCTX(SUMMARIZE(DataAllFunds,
			DataAllFunds[Date],
			DataAllFunds[Fund],
			"SumInstrContrib",
			sumx(DataAllFunds,[InstrPriceContr])),1+[SumInstrContrib])-1,ALLSELECTED(DataAllFunds[Date])))

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors