Investment Return Rate Calculation in Power BI

Today’s topics including:

1. Adding Index Column is very convenient to do complex calculation
2. How to use EARLIER to get previous row’s value (like LEAD/LAG in SQL)
3. How to use PRODUCTX to iterate multiplication
4. How to use Measure/Calculated Column to do above calculation

Scenario:

Bank Super-E has two key investment products: Good Money and Best Choice, and would like to check the performance of them. The indicators includes Monthly Return and Cumulative Return.

The formula are:

Monthly Return = Monthly Return(n)/Monthly Return(n-1) - 1

Cumulative Return = PRODUCT[1 + Monthly Return(1…n)] - 1

Sample Data and Preparation:

I create a spreadsheet with three columns, the value column is generated by RAND() in Excel, so apparently the performance of these two products of bank Super-E is very unstable :)

Load data to Power BI and add Index Column in Query Editor, I will let you know why we should take this action in the later calculation. Before indexing, another important step is Sort. Because the calculation is per Product, so I need to sort Column [Product] first ,then sort Column [Date].You could see the sorting sequence in the column name.

Add Index Column, it doesn’t matter about starting from 0 or 1.

Close and Apply, the preparation work is done.

Calculate Monthly Return:

As we know, the formula is Monthly Return(n)/Monthly Return(n-1) - 1, so I need previous month value to do this calculation. In SQL, we could easily use LAG function

LAG([Value],1) OVER (PARTITION BY [Product] ORDER BY [Date] DESC)

How can we do this calculation in Power BI?

One key DAX function is EARLIER

MS Official Documentation

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.

EARLIER is mostly used in the context of calculated columns.

So I create a calculated column based on below expression

PreValue = CALCULATE(MAX(Data[Value]),
                 ALL(Data),
                 Data[Product] = EARLIER(Data[Product]),
                 Data[Index] = EARLIER(Data[Index])+1
                 )

Data[Product] = EARLIER(Data[Product]), limit the calcualtion in same product group.

Data[Index] = EARLIER(Data[Index])+1, this is why I created index at first beginning.I invite Index as a input value and plus 1, which equals to the filtered row’s value, and there will be only one row meeting this filter condition. e.g. 1 = 0 + 1; 15 = 14 +1

MAX(Data[Value]), I just need a calculation function to get the value, as above explanation that only one is filtered, it could be SUM, MIN, etc.

Then I think you know how to calculate Monthly Return now, just add another calculated column based on formula

MonthlyReturn = IF(Data[PreValue] = BLANK(),
                   BLANK(),
                   (Data[Value]/Data[PreValue])-1)

I present the number in a simple Matrix with product as slicer. (Really unstable performance)

Calculate Cumulative Return:

The initial requirement I get from Bank Super-E is they could check N months Cumulative Return of any month they select. So, it must be a dynamic calculation, my solution is creating a measure to do this job.

Based on the formula, I need a function to do iteration of multiplication, and PRODUCTX could handle this type calculation. (Normally a DAX with X as Name End is iteration function, like SUM, SUMX)

MS Official Documentation

Returns the product of an expression evaluated for each row in a table.

The DAX expression will be

Investment Rate = CALCULATE(PRODUCTX(Data,1+Data[MonthlyReturn])-1,
                    FILTER(ALLSELECTED(Data),
                           COUNTROWS(FILTER(Data,
                                            Data[Product] = EARLIER(Data[Product])
                                            && Data[Index] >= EARLIER(Data[Index])))))

You will notice that I filter the data twice, because I am not able to directly use EARLIER function in CALCULATE function in a measure, and this is a trick to pass the nested logic from a table filter to a measure calculation.

I add a slide date slicer to the report as below, through moving the right bar, Bank Super-E could select one month to check cumulative return by counting down N months. The calculation is pure dynamic which meets my expectation.

But I get further requirement from Bank Super-E, they feel inconvenient to count and move the date bar. After negotiation, they would like to check fixed months cumulative return, like 3 months, 6 months, 1 year, etc. and the final output is only show one month per selecting drop down date slicer.

In this case, I could create calculated column to get fixed value of each month, but the iteration is for certain rows instead of whole product group.

Example: iterate three rows to calculate the result for 3 months cumulative return.

How can we do this?

3 Months = CALCULATE(PRODUCTX(Data,1+Data[MonthlyReturn])-1,
                    FILTER(Data,
                           Data[Product] = EARLIER(Data[Product])
                           && Data[Index] >= EARLIER(Data[Index])
                           && Data[Index] <= EARLIER(Data[Index])+2))

I limit the iteration range based on Index Column, and get column and final output as below

Now, Bank Super-E is very happy with this report:) Hope you are happy with this content as well!

Thanks Eric Dong