Dynamic weighted sum of securities' compounded returns across time

[hypertog824]

Hi,

I am working with daily financial data of an index of securities. I would like to have a DAX expression that calculates what index returns would be compounded over time. I know this can be achieved with a CALCULATE function on ALLSELECTED(FILTER(date)) using PRODUCTX(table, <compounding return formula>) but I don't know how to modify this DAX expression to account for security-level data. My data looks like this:

Date	Security	Return	Market Value
1-Jan-19	ABC	0.0901	1000
1-Jan-19	DEF	0.0332	500
1-Jan-19	GHI	0.0086	300
2-Jan-19	ABC	0.0113	1000
2-Jan-19	DEF	0.0885	500
2-Jan-19	GHI	0.0259	300
3-Jan-19	ABC	0.0835	1000
3-Jan-19	DEF	0.0287	500
3-Jan-19	GHI	0.0999	300

I want Power BI to calculate a table that looks like this:

Date	Return on Date
1-Jan-19	1.0607
2-Jan-19	1.0973
3-Jan-19	1.1747

Which is really a sum of the compounded returns of each security weighted by their market value up to the latest date. To elaborate:

Jan 1's value = 1.0607 = 1*(1 + 0.0901)*(1000/1800) + 1*(1 + 0.0332)*(500/1800) + 1*(1 + 0.0086)*(300/1800) 

Jan 2's value = 1.0973 = 1*(1 + 0.0901)*(1 + 0.0113)*(1000/1800) + 1*(1 + 0.0332)*(1 + 0. 0.0885)*(500/1800) + 1*(1 + 0.0086)*(1 + 0.0259)*(300/1800) 

And so forth. Any help would be greatly appreciated!

[dax]

Hi @hypertog824 , 

You could refer to my sample for details.

Measure 2 = sumx(SUMMARIZE('Table (2)','Table (2)'[Date],'Table (2)'[Security],'Table (2)'[return+1],'Table (2)'[Market Value],"mul", SUM('Table (2)'[Market Value])*CALCULATE(PRODUCT('Table (2)'[return+1]), filter(ALLEXCEPT('Table (2)','Table (2)'[Security]), 'Table (2)'[Date]<=MIN('Table (2)'[Date])))),[mul])/SUM('Table (2)'[Market Value])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hypertog824]

Thanks so much! Is there any way to cause the calculation to be dynamic if the date filter is adjusted? For example, if I filter out January 1st, the value for Jan 2 would adjust from 1.0973 to 1.013053? 

Basically what would happen is that the filter would then cause the Jan 2nd calculation to change:

Return on Jan 2 = 1.013053 = 1*(1 + 0.0901)*(1 + 0.0113)*(1000/1800) + 1*(1 + 0.0332)*(1 + 0. 0.0885)*(500/1800) + 1*(1 + 0.0086)*(1 + 0.0259)*(300/1800) 

Thanks again for your help.

[dax]

Hi @hypertog824 , 

I need to check your logic with you, if I select Jan 2 in slicer, Jan 1 =0, Jan2=1.01? If not, please explain your logic to me in details?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.