cumulative portfolio returns

[Anonymous]

I am trying to calculate the cumulative portfolio returns for a fund (GHY Monthly Returns) and an Index (BAML HW00) - see sample of the data. I need to be able to vary the measurement over multiple time frames e.g Quarter to date, year to date, calendar year, rolling 12 months. 

[Ashish_Mathur]

Hi,

What result are you expecting?  Please show them in a speprate table.  Also, share the link from where i can download your file.

[Anonymous]

Thanks. I have attached a photo of an excel file which shows how I would work out the numbers in excel. I am struggling to see who I can achieve this in dax. Cumulative returns are not simply the sum of returns over a given period each month's return is multiplied by (1+Previous Months return).

I would like to calculate the YTD, QTD and returns over a variety of time periods eg. Last 18 months, rolling 12 months etc. 

[quickcreator]

Did you ever get an answer to this? I'm faced with the same challenge.

[v-chuncz-msft]

@Anonymous,

You may refer to the following DAX that adds a calculated column.

Cumulative Return =
SUMX (
    FILTER ( Table1, Table1[Date] <= EARLIER ( Table1[Date] ) ),
    Table1[Return]
)

[Ashish_Mathur]

Hi,

While i am eager to help, i still cannot visualise the result that you want.  Please share the following:

1. The exact structure
2. Fill up actual numbers in that exact structure based on the summyh data that you share

I will then write the DAX formulas to get your desired result.

[Anonymous]

This is the file that I am using. On the right hand side is a time series of returns for a fund and its index and relative performance. Using the return data I need to be able to calculate the return for each calendar year (the return should be annualised and cumulative for each time period) and for multiple time periods - this could be 6 months, 12 months, rolling 12 months, financial year. I also need to work out a way of rebasing the chart on left side so that it always rebases to 0 when I can the date. 

https://www.dropbox.com/s/jqt3hwtb4iywbya/Return%26Risk%20Reporting.pbix?dl=0

If this isn't the data form you need just let me know. 

[Anonymous]

Hi @Anonymous 

Did you manage to find a solution. This is exactly what I am looking for.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may take a good look at this article.