Medidas a tener en cuenta el contexto de fila y las fechas para el cálculo

[Syndicate_Admin]

Hola, es un problema largo, pero espero explicarlo bien. Comencemos con los datos sin procesar:

(Disculpas por no poder formatear la tabla correctamente aquí.)

fecha	grupo	Grupo de padres	IrrelevanteColumn	devolución	Índice de retorno	Índice de rendimientos acumulados	Prev_Month_Cumulative_Index
31-Dic-15	A	PG1	abecedario	0.00%	1.000	1.000	en
31-Dic-15	B	PG1	abecedario	0.00%	1.000	1.000	en
31-Dic-15	C	PG1	abecedario	0.00%	1.000	1.000	en
31-Dic-15	D	PG1	abecedario	0.00%	1.000	1.000	en
31-Dic-15	E	PG2	abecedario	0.00%	1.000	1.000	en
31-Dic-15	F	PG2	abecedario	0.00%	1.000	1.000	en
31-Dic-15	G	PG2	abecedario	0.00%	1.000	1.000	en
31-Dic-15	H	PG2	abecedario	0.00%	1.000	1.000	en
29-Enero-16	A	PG1	abecedario	4.61%	1.046	1.046	1.000
29-Enero-16	B	PG1	abecedario	-3.57%	0.964	0.964	1.000
29-Enero-16	C	PG1	abecedario	-1.79%	0.982	0.982	1.000
29-Enero-16	D	PG1	abecedario	-5.97%	0.940	0.940	1.000
29-Enero-16	E	PG2	abecedario	0.06%	1.001	1.001	1.000
29-Enero-16	F	PG2	abecedario	4.51%	1.045	1.045	1.000
29-Enero-16	G	PG2	abecedario	-4.54%	0.955	0.955	1.000
29-Enero-16	H	PG2	abecedario	-3.79%	0.962	0.962	1.000
29-Febrero-16	A	PG1	abecedario	2.12%	1.021	1.068	1.046
29-Febrero-16	B	PG1	abecedario	-5.51%	0.945	0.911	0.877
29-Febrero-16	C	PG1	abecedario	-3.34%	0.967	0.949	1.009
29-Febrero-16	D	PG1	abecedario	4.30%	1.043	0.981	0.991
29-Febrero-16	E	PG2	abecedario	-7.68%	0.923	0.924	0.931
29-Febrero-16	F	PG2	abecedario	-4.95%	0.950	0.993	0.932
29-Febrero-16	G	PG2	abecedario	-2.13%	0.979	0.934	0.974
29-Febrero-16	H	PG2	abecedario	0.37%	1.004	0.966	0.930
31-Mar-16	A	PG1	abecedario	-2.48%	0.975	1.042	0.895
31-Mar-16	B	PG1	abecedario	-2.94%	0.971	0.884	0.847
31-Mar-16	C	PG1	abecedario	-1.03%	0.990	0.939	0.884
31-Mar-16	D	PG1	abecedario	0.31%	1.003	0.984	0.816
31-Mar-16	E	PG2	abecedario	3.84%	1.038	0.959	0.776
31-Mar-16	F	PG2	abecedario	4.93%	1.049	1.042	0.759
31-Mar-16	G	PG2	abecedario	6.88%	1.069	0.998	0.762
31-Mar-16	H	PG2	abecedario	1.69%	1.017	0.982	0.743

datos brutos

Para empezar, las columnas [Cumulative_Returns_Index] y [Prev_Month_Cumulative_Index] no forman parte de los datos sin procesar, pero las creé como columnas auxiliares en Excel para facilitar la explicación de cuáles deben ser los resultados. Si es posible, por favor hágamelo saber cómo crearlos ya sea como columnas calculadas, o hacer todo como medidas.

Declaración de problemas

Me gustaría crear una medida que tendrá en cuenta un intervalo de fechas (definido a través de una segmentación de datos) y, a continuación, calcular los rendimientos acumulativos del grupo, rebasados en la fecha más temprana del intervalo de fechas. He desglosado el problema tanto como puedo para tratar de hacer más fácil obtener ayuda:

1. Busque la fecha más pequeña en el intervalo de fechas (esta fecha más pequeña será común en todos los grupos)
   • FIRSDATE() / MINX() ?
   • Filtrar por grupos (proporcionado como contexto de fila)
   • Busque los retornos acumulativos hasta la fecha, rebasados en los rendimientos acumulativos de la fecha más temprana.
     • Por ejemplo, para el Grupo A por un período de enero a marzo, sus rendimientos acumulados serían los siguientes, ya que actualmente se basa en enero (más específicamente, el comienzo de enero/finales de diciembre):
       • (Implícito) 1-Enero: 1.000
       • 29-Enero: 1.046
       • 29-Febrero: 1.068
       • 31-Mar: 1.042
     • Para un período de febrero a marzo, el mismo Grupo A regresará:
       • (Implícito) 1-Feb: 1.000 <- 1.046/1.046
       • 29-Feb: 1.021 <- 1.068/1.046
       • 31-Mar: 0.996 <- 1.042/1.046

Producción esperada

La salida esperada, junto con los otros grupos presentes (usando sólo A, B y C para evitar que la lista se alarga demasiado), debería tener este aspecto:

Si se selecciona el intervalo de fechas de enero a marzo para los grupos A&B:

Si se selecciona el intervalo de fechas de febrero a marzo para los grupos A, B y C:

Cosas que he intentado:

He creado el [Cumulative_Returns_Index] y [Prev_Month_Cumulative_Index] a través de Python antes de importar los datos a PowerBI (pero me gustaría procesar los datos sin procesar si es posible), y he utilizado muchas horas para idear el siguiente código que no funcionó cuando más de 1 grupo estaba presente en la visualización (tabla). Sospecho que se debe a varios ref_indexes que se devuelven, pero PowerBI de alguna manera no pudo identificar los grupos correctos para usarlo a través del contexto de fila proporcionado en la visualización.

El siguiente es el código de todos modos, si ayuda:

Rebal_Cumu_Returns_AllSel = 
VAR ref_index =
    CALCULATE(
        VALUES( table[Prev_Mth_Cumulative_Returns_Index]), 
        FILTER( ALLSELECTED(table), 
                table[Date] = MINX(ALLSELECTED(table), table[Date]))
    )
RETURN
    DIVIDE(SELECTEDVALUE(table[Cumulative_Returns_Index]), ref_index)

Caso de uso final

Espero eventualmente utilizar esto en un gráfico de líneas, con retornos acumulativos como el eje Y y la fecha como un eje X. Es probable que los intervalos de fechas se alterne a través de una segmentación de datos.

Por favor, hágamelo saber si hay algo más que pueda proporcionar.

[Syndicate_Admin · Accepted solution]

Ya que no puedo por alguna razón pegar código formateado aquí... bueno, tendrás que ver con una foto. arrepentido.

[Syndicate_Admin]

Hola, gracias por dar una respuesta. Sólo quería aclarar - ¿Indica Date[Year-Month] que tengo que crear una tabla que resuma la lista de fechas que tengo en mi tabla de hechos en el formato YYYY-MM?

Además, en la línea 40 anterior, se supone que ALLSELECTED(Dates) es ALLSELECTED(DateTable), donde DateTable hace referencia a la tabla antes mencionada creada anteriormente en el formato AAAA-MM?

Para responder a su pregunta inicial, sí, la jerarquía es natural. ¿Significa que la variable onlyOneParentGroupVisible ya no es necesaria?

[Syndicate_Admin]

Hola sólo quería añadir - ya que la tabla de hechos por naturaleza tiene un mes único a lo largo de todos los años, parecía que la mesa de fechas podría ser descartada también. He enmendado la fórmula en consecuencia, y parece que funciona. Gracias @daxer !

La fórmula final que usé fue:

Cumulative_Returns_Index_Rebased = 
VAR isGroupInScope = ISINSCOPE( table[Group] )
VAR shouldCalculate = True
    && isGroupInScope
VAR Result =
    IF( shouldCalculate,
        VAR firstCumulativeReturnsIndex = 
            MAXX( 
                CALCULATETABLE(
                    TOPN( 1,
                        table,
                        table[Date],
                        ASC
                    ),
                    ALLSELECTED( table[Date] )
                ),
                table[Cumulative_Returns_Index]
            )
        VAR currentCumulativeIndex = 
            SELECTEDVALUE( table[Cumulative_Returns_Index] )
        VAR relativeCumulativeIndex =
            DIVIDE( currentCumulativeIndex, 
                    firstCumulativeReturnsIndex
            )
        RETURN
            relativeCumulativeIndex
    )
RETURN
    Result

Si hay alguna preocupación con respecto a los cambios que he hecho (para excluir la creación de una tabla de fechas y la comprobación del grupo primario) que usted piensa que potencialmente podría conducir a problemas en la línea, hágamelo saber.

salud.

[Syndicate_Admin]

@Wendeley-Norte

Todo lo que puedo decirte es esto. Si tiene un modelo en el que no tiene tablas separadas para cotas, especialmente la dimensión de fecha y hora... entonces 100% lo estás haciendo totalmente mal. No te sorprendas cuando algún día descubras que tus cifras no siempre son correctas. Pero dado que es muy difícil detectar inmediatamente los problemas que sin duda tendrá un día (¡porque esto depende - sorpresa, sorpresa! - de la distribución de datos en columnas), basará sus decisiones en cifras equivocadas.

Es tu decisión no seguir mi consejo. Sobre www.sqlbi.com el suyo es un artículo de Alberto Ferrari que explica por qué el modelo de una mesa en ciertas circunstancias producirá cifras equivocadas y no se puede hacer nada al respecto aparte de... construir un modelo correcto, lo que significa STAR-SCHEMA.

[Syndicate_Admin]

Lo tomaré en cuenta y lo investigaré más, incluyendo la lectura de algunos artículos sobre sqlbi.com. Muchas gracias una vez más. salud.

[Syndicate_Admin]

Primera pregunta. ¿Es natural el grupo primario de jerarquía -> Agrupar? Una jerarquía natural es aquella que tiene hojas únicas. Por lo tanto, por ejemplo, no puede tener ninguna hoja (piensa: Grupo) debajo de 2 grupos primarios diferentes. ¿Es esto cierto? Porque si no es así, entonces usted tiene que decir cómo desea calcular cuando el mismo grupo, por ejemplo A, está visible en el contexto actual, pero no se ha seleccionado ningún grupo primario.