Devoluciones de cartera continua con tamaño de ventana personalizable y tamaño de paso

[Anonymous]

¡Hola comunidad!

Tengo en mi modelo de Power BI una tabla larga con varias carteras y sus rendimientos mensuales, en el siguiente formato (que no me importa ajustar):

Enlace de Excel e imagen de instantánea:

cartera de pruebas returns.xlsx

Ver columna A, B y C: (2 Parámetros en la columna I)

En el paso 1, necesito la salida en la columna E: retornos de balanceo anualizados basados en un tamaño de ventana (por ejemplo, 4 meses), calculado como Producto (1 + devoluciones mensuales en la ventana móvil) (12 / tamaño de ventana)-1)

(una fórmula de matriz ctrl+shift+enter en Excel)

Necesito agregarlo escalonando un cierto número de meses, diferente del tamaño de la ventana, lo que lleva a la columna F: la diferencia entre la cartera y el rendimiento móvil anualizado del benchmark para el mismo período escalonado.

Al final, quiero calcular

i) el porcentaje de números positivos en la columna F, y

ii) el promedio de la columna F - agregado por cartera.

No estoy seguro de si usar medidas o columnas de DAX o ambas.

Para calcular los retornos de balanceo para todos los meses, he utilizado correctamente la siguiente medida:

(Tenga en cuenta que Return_Fund1 es una columna con la columna de retorno + 1, no es ideal, pero no podía hacer el +1 dentro del producto como podría en Excel - cualquier consejo sobre esta bienvenida!)

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

A continuación, puedo identificar las fechas que se usará para el tamaño de paso mediante la siguiente columna DAX:

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

El gran desafío es conseguir la columna E solo para los meses identificados donde la columna anterior es 1. Intenté lo siguiente, pero acabo de obtener todos los valores sin los huecos necesarios:

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

(Configuro una tabla en la vista de informe con Nombres y fechas de cartera como columnas, junto con las medidas / columnas que calculé)

Recientemente comencé a aprender DAX, pero no puedo envolver mi cabeza alrededor de cómo agregar y usar las fechas correctamente para este problema.

Tenga en cuenta que tengo 450k filas, por lo que la solución debe ser lo más eficiente posible.

¡La ayuda es muy apreciada!

Ruan

[v-shex-msft · Accepted solution]

Hola @quanalyze,

AFAIK, las tablas de modelo de datos power bi no contienen índice de fila/columna que puede extraer directamente un valor de celda específico, las funciones dax tampoco contienen desplazamiento, me gustaría sugerirle que calcule el retorno continuo en el lado de Excel e impórtelos a power bi para las siguientes operaciones.

Pasos:

1. Agregue calcular diferencia de columna a table1 para comparar entre dos tablas rolling return.

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

2. Escriba una tabla calculada para resumir los registros de tabla para generar el resultado esperado que deseaba.

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]
)

saludos

Xiaoxin Sheng