Rolling portfolio returns with customizable window size and step size

[Anonymous]

Hi Community!

I have in my Power BI model a long table with multiple portfolios and their monthly returns, in the following format (which I don't mind adjusting):

Excel link & snapshot image:

test portfolio returns.xlsx  

See column A, B & C:    (2 Parameters in column I)

In Step 1, I need the output in column E: annualized rolling returns based on a window size (eg 4 months), calculated as Product(1+monthly returns in rolling window)^(12/window size)-1) 

(a ctrl+shift+enter array formula in Excel)

I need to aggregate it by stepping a certain number of months, different from the window size, which leads to column F: the difference between the portfolio and the benchmark's annualized rolling return for the same stepped period. 

In the end, I want to calculate

   i) the % of positive numbers in column F, and

   ii) the average of column F - aggregated per portfolio.

I'm not sure whether to use DAX measures or columns or both. 

To calculate the rolling returns for all months, I've successfully used the following measure:

(note that Return_Fund1 is a column with the return column + 1, not ideal, but I couldn't do the +1 inside Product like I could in Excel - any advice on this welcome!)

Rolling Returnss Annualized = 
var windowSize = SUM(RollingWindowSize[RollingWindowSize])
return
CALCULATE(
    PRODUCT(testReturns[Return_Fund1]) , DATESINPERIOD(testReturns[Return Date], LASTDATE(testReturns[Return Date]) , - windowSize , MONTH)
    )
^ (12 / windowSize) - 1

I can then identify which dates to use for the step size by using the following DAX column:

Steps _should calculate  = 
IF( MOD( DATEDIFF(testReturns[Return Date], LASTDATE(WindowEndDate[WindowEndDate]), MONTH) , SUM(RollingWindowSize[RollingWindowSize]) ) = 0 , 1, 0)

The big challenge is getting column E only for the months identified where the above column =1. I tried the following but I just get all the values without the required gaps:

Rolling Returns SteppingX = 
var stepSize = SUM(RollingStepSize[RollingStepSize])
return
CALCULATE(
    testReturns[Rolling Returnss Annualized] , DATESINPERIOD(testReturns[Return Date] , LASTDATE(testReturns[Return Date]) , -stepSize, MONTH) )

(I set up a table in the report view with Portfolio names & Dates as columns, together with the measures / columns I calculated)

I recently started learning DAX, but can't seem to wrap my head around how to aggregate and use the dates properly for this problem. 

Note that I have 450k rows, so the solution should be as efficient as possible.

Help is much appreciated!

Ruan

[v-shex-msft · Accepted solution]

Hi @Anonymous,

AFAIK, power bi data model tables do not contain row/column index that you can direct extract specific cell value, Dax functions also not contains offset, I'd like to suggest you calculate the rolling return at the excel side and import them to power bi for the following operations.

Steps:

1. Add calculate column diff to table1 to compare between two tables rolling return.

Diff = [Rolling Return]- LOOKUPVALUE(T2[Rolling return],T2[Index],T1[Index])

2. Write a calculated table to summarize table records to output the expected result that you wanted.

Table = 
ADDCOLUMNS (
    SUMMARIZE (
        T1,
        [Name],
        "pCount",
            COUNTROWS ( FILTER ( T1, [Name] = EARLIER ( T1[Name] ) && [Diff] > 0 ) ),
        "Count",
            COUNTROWS ( FILTER ( T1, [Name] = EARLIER ( T1[Name] ) && [Diff] <> BLANK () ) ),
        "AVG", AVERAGEX ( FILTER ( T1, [Name] = EARLIER ( T1[Name] ) ), [Diff] )
    ),
    "Percent", [pCount] / [Count]
)

Regards,

Xiaoxin Sheng