What is the RRI Function?
The RRI function is a DAX function that calculates the relative rate of return for an investment over a given period of time. It does this by taking into account the initial investment, the final value of the investment, and the time period in which the return was earned. The function returns a decimal value that represents the annual rate of return on the investment.
How Does the RRI Function Work?
The RRI function takes three arguments:
1. The initial investment (or present value).
2. The final value of the investment (or future value).
3. The time period (in years) over which the return was earned.
Here’s the syntax for the RRI function:
RRI(initial_investment, final_value, time_period)
The function then calculates the relative rate of return using the following formula:
RRI = (final_value / initial_investment) ^ (1 / time_period) – 1
Let's break down this formula. The first part, `(final_value / initial_investment)`, calculates the total return on the investment. The second part, `^(1 / time_period)`, raises this return to the power of 1 divided by the time period. This calculates the annual rate of return. Finally, we subtract 1 from this value to get the relative rate of return.
Using the RRI Function in Power BI
Now that we understand how the RRI function works, let's look at how to use it in Power BI. We'll use a simple example to demonstrate the function.
Suppose we have the following data:
| Investment | Initial Value | Final Value | Time Period |
| Investment 1 | $10,000 | $12,000 | 3 |
| Investment 2 | $5,000 | $7,500 | 5 |
To calculate the relative rate of return for each investment, we'll create a new measure in Power BI using the RRI function.
1. Open the Power BI desktop application and load your data.
2. Click on the "New Measure" button in the "Modeling" tab.
3. In the formula bar, enter the following formula to calculate the RRI for Investment 1:
RRI1 = RRI(10000, 12000, 3)
4. Press enter to create the measure.
5. Repeat steps 3-4 for Investment 2:
RRI2 = RRI(5000, 7500, 5)
6. Now, you can add these measures to your visualizations to display the relative rate of return for each investment.
The RRI function in DAX can help you calculate the relative rate of return on an investment over a given time period. By understanding how the function works and how to use it in Power BI, you can easily calculate this important financial metric for your data.