Calculating Investment Rate of Return in Power BI

[petrosk]

I have a Fact Table of Net Asset Values (NAV) for investments over time.  My objective is to calculate the Rate of Return (ROR), based on a user-defined start date and end date.  The ROR depends on the start date, end date, and investment changes during the period being analyzed.  For simplicity, the model excludes the effect of dividends.  I have also copied/pasted below the key formulas along with a mock-up of the Fact Table.  The last column, entitled NAV T-1 Month, is calculated.  My Questions:

1. How might I adapt the model and/or formulas so the user can define the start date and end date? Would variables work for this purpose?
2. How might I create formulas to calculate the 1-month, 6-month, and 12-month rate of return?

Any help from the community would be greatly appreciated.

NAV T-1 Month = LOOKUPVALUE(FactFundNAV[NAV],FactFundNAV[Date],PREVIOUSMONTH(FactFundNAV[Date]),FactFundNAV[Fund Code],FactFundNAV[Fund Code])

Delta = IFERROR((CALCULATE(SUM(FactFundNAV[NAV]))-CALCULATE(SUM(FactFundNAV[NAV T-1 Month])))/CALCULATE(SUM(FactFundNAV[NAV T-1 Month])),Blank())

ROR = EXP(SUMX(FILTER(ALL( FactFundNAV[Date] ),FactFundNAV[Date] <= MAX( FactFundNAV[Date])),LN(1+[Delta])))-1

Date	Fund Code	NAV	NAV T-1 Month
12/31/2014	A	8.14
12/31/2014	B	27.15
12/31/2014	C	20.63
1/30/2015	A	8.19	8.14
1/30/2015	B	26.71	27.15
1/30/2015	C	20.65	20.63
2/27/2015	A	8.35	8.19
2/27/2015	B	28.06	26.71
2/27/2015	C	21.9	20.65
3/31/2015	A	7.87	8.35
3/31/2015	B	26.48	28.06
3/31/2015	C	21.04	21.9
4/30/2015	A	7.94	7.87
4/30/2015	B	26.43	26.48
4/30/2015	C	21.95	21.04
5/29/2015	A	7.96	7.94
5/29/2015	B	26.82	26.43
5/29/2015	C	21.9	21.95
6/30/2015	A	7.86	7.96
6/30/2015	B	26.09	26.82
6/30/2015	C	21.3	21.9
7/31/2015	A	7.88	7.86
7/31/2015	B	26.53	26.09
7/31/2015	C	21.05	21.3
8/31/2015	A	7.8	7.88
8/31/2015	B	25.07	26.53
8/31/2015	C	19.3	21.05
9/30/2015	A	7.64	7.8
9/30/2015	B	24.61	25.07
9/30/2015	C	18.49	19.3
10/30/2015	A	7.84	7.64
10/30/2015	B	26.67	24.61
10/30/2015	C	20.23	18.49
11/30/2015	A	7.73	7.84
11/30/2015	B	26.76	26.67
11/30/2015	C	20.33	20.23
12/31/2015	A	7.59	7.73
12/31/2015	B	26.64	26.76
12/31/2015	C	19.8	20.33
1/29/2016	A	7.51	7.59
1/29/2016	B	24.97	26.64
1/29/2016	C	18.23	19.8
2/29/2016	A	7.55	7.51
2/29/2016	B	24.69	24.97
2/29/2016	C	17.92	18.23
3/31/2016	A	7.35	7.55
3/31/2016	B	25.16	24.69
3/31/2016	C	18.73	17.92
4/29/2016	A	7.54	7.35
4/29/2016	B	25.04	25.16
4/29/2016	C	19.12	18.73
5/31/2016	A	7.54	7.54
5/31/2016	B	25.81	25.04
5/31/2016	C	19.1	19.12
6/30/2016	A	7.59	7.54
6/30/2016	B	25.24	25.81
6/30/2016	C	18.87	19.1
7/29/2016	A	7.75	7.59
7/29/2016	B	26.86	25.24
7/29/2016	C	19.93	18.87
8/31/2016	A	7.89	7.75
8/31/2016	B	27.18	26.86
8/31/2016	C	20.34	19.93
9/30/2016	A	7.93	7.89
9/30/2016	B	27.66	27.18
9/30/2016	C	20.92	20.34
10/31/2016	A	7.95	7.93
10/31/2016	B	26.86	27.66
10/31/2016	C	20.28	20.92
11/30/2016	A	7.89	7.95
11/30/2016	B	27.87	26.86
11/30/2016	C	19.46	20.28
12/30/2016	A	7.99	7.89
12/30/2016	B	28.36	27.87
12/30/2016	C	19.53	19.46
1/31/2017	A	8.08	7.99
1/31/2017	B	29.24	28.36
1/31/2017	C	20.94	19.53

[v-ljerr-msft · Accepted solution]

Hi @petrosk,

1. How might I adapt the model and/or formulas so the user can define the start date and end date? Would variables work for this purpose?

---

Using variables could be an option. The formula below is for your reference. 

ROR = 
VAR maxSelectedDate =
    MAX ( FactFundNAV[Date] )
VAR minSelectedDate =
    MIN ( FactFundNAV[Date] )
RETURN
    EXP (
        SUMX (
            FILTER (
                ALL ( FactFundNAV[Date] ),
                FactFundNAV[Date] <= maxSelectedDate
                    && FactFundNAV[Date] >= minSelectedDate
            ),
            LN ( 1 + [Delta] )
        )
    )
        - 1

1. How might I create formulas to calculate the 1-month, 6-month, and 12-month rate of return?

If I understand this correctly, the formula below may do it in a simple way.

1 Month ROR = 
VAR maxDate =
    CALCULATE ( MAX ( FactFundNAV[Date] ), ALL ( FactFundNAV ) )
RETURN
    EXP (
        SUMX (
            FILTER (
                ALL ( FactFundNAV[Date] ),
                FactFundNAV[Date] <= maxDate
                    && FactFundNAV[Date]
                    >= maxDate - 30
            ),
            LN ( 1 + [Delta] )
        )
    )
        - 1

Regards

[GilbertQ]

Hi @petrosk

What you are asking can certainly be done.

For Question 1, I would suggest that you create your measures that are independant of dates where they need to be. The reason for this is so that when the user selects a date range, the measure will then calculate on the fly based on the date range selected.

For Question 2, I would suggest that you can put in disconnected slicers, which can then filter your data based on what the user selects? Another option is to build in a period table, which can do this for you.

Here is a blog post: Create Dynamic Periods for Fiscal or Calendar Dates in Power BI