Calculating Product of Instrument Contributions

[Johnsnowlife]

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

[v-yuezhe-msft]

@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

[Johnsnowlife]

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. 

[v-yuezhe-msft]

@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

[Johnsnowlife]

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. 

[Johnsnowlife]

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

[Johnsnowlife]

Try this link