cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

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.

Re: Calculating Product of Instrument Contributions

Try this link

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. 

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors