
1. Re: Calculating MTD, QTD, and YTD cumulative returns???
Benjamin Greene Sep 26, 2016 12:24 PM (in response to luciana.suran.1)You can do this with advanced table calculation specification (and without creating any more fields!). Here's what to do:
1. In a new sheet, create a Cumulative Return calculated field using the syntax you provided above.
2. Drag Measure Names to Filters and select Return and Cumulative Return.
3. Drag Measure Names to Columns.
4. Rightclick drag Date to Rows, then select Date (Discrete).
5. Drag YEAR(Date) to Detail, then click the plus sign to drill down to QUARTER(Date) and MONTH(Date).
6. Drag Measure Values to Text.
7. Rightclick the Cumulative Return pill and select Compute Using>Date.
8. Drag another instance of Cumulative Return to the Measure Values shelf.
9. Rightclick this new Cumulative Return pill and select Edit Table Calculation.
10. Compute using Date at the deepest level restarting every Year of Date and click OK.
11. Drag another instance of Cumulative Return to the Measure Values shelf.
12. Rightclick this new Cumulative Return pill and select Edit Table Calculation.
13. Compute using Date at the deepest level restarting every Quarter of Date and click OK.
14. Drag another instance of Cumulative Return to the Measure Values shelf.
15. Rightclick this new Cumulative Return pill and select Edit Table Calculation.
16. Compute using Date at the deepest level restarting every Month of Date and click OK.
17. Edit the aliases of the columns to differentiate the Total Cumulative Return from YTD, QTD and MTD.
And there you have it!

CumulativeReturn.twbx 106.5 KB


2. Re: Calculating MTD, QTD, and YTD cumulative returns???
Shinichiro Murakami Sep 26, 2016 12:33 PM (in response to luciana.suran.1)Because you are handling three different time range, you need to create three different work sheets and combine them on the dashboard.
Each worksheet has filter to show only latest Year, Qtr, and Month by LOD. plus only how the last date of the latest date by index "last()".
Thanks,
Shin

CumulativeReturn_SM_9.3.twbx 121.8 KB


3. Re: Calculating MTD, QTD, and YTD cumulative returns???
luciana.suran.1 Sep 26, 2016 2:19 PM (in response to Benjamin Greene)Thanks a million!

4. Re: Calculating MTD, QTD, and YTD cumulative returns???
Matthew Riley Jul 3, 2017 11:28 AM (in response to luciana.suran.1)The attached workbook s is very nearly correct but there are a couple of mistakes.
1. The total, YTD, QTD and MTD total returns need to start at the previous date. So for the first row, date = 24 Sep 2002, the first return is 1.869%, so the first cumulative return should be 0.98131 = (11.869%)
2. The cumulative return is actually the NAV or price, or 1+the cumulative return. So the cumulative return of returns 1% then 2% is actually 3.02%, = (1+1%)*(1+2%)1. The calculated return should subtract 1. I think the revised calculation should be, cumulative return=:
IF INDEX() = 0
THEN 0
ELSE
(1 + AVG([Return])) * (1+ PREVIOUS_VALUE(0))1
END
I've attached a revised workbook. The original is renamed "Original", with my revised version in the worksheet "Revised". The revised cumulative return calculation is called "New cumulative return".
Hope this helps  please let me know if you see any mistakes.

CumulativeReturn  revised.twbx 139.6 KB
