Valor total de mayor ejecución en los últimos X días

[mjk1992]

Hola

He estado luchando durante bastante tiempo con cómo trabajar esto. Tengo una medida de trabajo que calcula el total de ejecución (LTD PL) de Ganancia/Pérdidas (DTD PL) por Día.

LTD PL = 
IF(
    COUNTROWS ('Model Table') > 0,
    CALCULATE (SUM(
    'Model Table'[DTD PL]),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE]),
        'Date Table'[REPORTDATE] <= MAX ( ( 'Date Table'[REPORTDATE]) ))),
        BLANK()
)

El objetivo es crear una medida separada que tome el total de carrera más alto en los últimos X días (digamos 10 para este ejemplo) y lleve este "pico" hacia adelante hasta que se alcance un nuevo total de carrera alto o 2) los 10 días pasen sin un nuevo máximo por lo que se devuelve el siguiente total de carrera más alto. Lo mismo se aplicaría para el más bajo, o "vaguada".

He probado una solución que utiliza "RankX" sin embargo antes de que pudiera conseguir de forma fiable para que funcione dinámicamente (quiero que esto funcione en todos los

Ejemplo de FactData
REPORTDATE	Cartera	DTD PL
6/1/2020	A	5,404
6/1/2020	B	(149)
6/2/2020	A	2,244
6/2/2020	B	(1,999)
6/3/2020	A	3,228
6/3/2020	B	3,503
6/4/2020	A	659
6/4/2020	B	8,220
6/5/2020	A	(1,244)
6/5/2020	B	(741)
6/8/2020	A	(2,945)
6/8/2020	B	(5,675)
6/9/2020	A	7,820
6/9/2020	B	(1,086)
6/10/2020	A	(4,372)
6/10/2020	B	2,873
6/11/2020	A	(6,874)
6/11/2020	B	2,848
6/12/2020	A	(6,493)
6/12/2020	B	1,013
6/15/2020	A	7,773
6/15/2020	B	(7,354)
6/16/2020	A	(7,152)
6/16/2020	B	(5,025)
6/17/2020	A	(9,775)
6/17/2020	B	(3,712)
6/18/2020	A	2,995
6/18/2020	B	(6,380)
6/19/2020	A	(3,357)
6/19/2020	B	(8,309)
6/22/2020	A	(2,189)
6/22/2020	B	(4,214)
6/23/2020	A	9,924
6/23/2020	B	7,684
6/24/2020	A	5,452
6/24/2020	B	(9,514)
6/25/2020	A	(8,631)
6/25/2020	B	(9,700)
6/26/2020	A	(5,478)
6/26/2020	B	9,536
6/29/2020	A	4,635
6/29/2020	B	(4,658)
6/30/2020	A	(4,937)
6/30/2020	B	9,077

Salida de tabla de Power BI prevista (mediante la cartera A)
REPORTDATE	DTD PL	LTD PL	Rolling 10-Day LTD Peak	Rolling 10-Day LTD Trough
6/1/2020	5,404	5,404	5,404	5,404
6/2/2020	2,244	7,648	7,648	5,404
6/3/2020	3,228	10,876	10,876	5,404
6/4/2020	659	11,535	11,535	5,404
6/5/2020	(1,244)	10,291	11,535	5,404
6/8/2020	(2,945)	7,346	11,535	5,404
6/9/2020	7,820	15,166	15,166	5,404
6/10/2020	(4,372)	10,794	15,166	5,404
6/11/2020	(6,874)	3,920	15,166	3,920
6/12/2020	(6,493)	(2,573)	15,166	(2,573)
6/15/2020	7,773	5,200	15,166	(2,573)
6/16/2020	(7,152)	(1,952)	15,166	(2,573)
6/17/2020	(9,775)	(11,727)	15,166	(11,727)
6/18/2020	2,995	(8,732)	15,166	(11,727)
6/19/2020	(3,357)	(12,089)	15,166	(12,089)
6/22/2020	(2,189)	(14,278)	15,166	(14,278)
6/23/2020	9,924	(4,354)	15,166	(14,278)
6/24/2020	5,452	1,098	10,794	(14,278)
6/25/2020	(8,631)	(7,533)	5,200	(14,278)
6/26/2020	(5,478)	(13,011)	5,200	(14,278)
6/29/2020	4,635	(8,376)	5,200	(14,278)
6/30/2020	(4,937)	(13,313)	1,098	(14,278)

Salida de gráfico de Power BI prevista

Espero haber proporcionado suficiente información para describir mis resultados previstos. Por favor, hágamelo saber si podría proporcionar más información y cualquier ayuda es apreciada. ¡Gracias!

[mjk1992]

Me di cuenta de que mi borrador no guardó cierta información antes de publicar. Los datos de ejemplo están algo alineados con la forma en que se presenta mi conjunto de datos en mi consulta en Power BI. La tabla y el gráfico es como me gustaría que se viera la salida en Power BI. He probado todas las funciones MAXX( mostradas en preguntas anteriores, sin embargo, no he tenido suerte en conseguir que funcionen según lo previsto.

[mahoneypat]

Por favor, pruebe esta expresión. Como su columna DTD PL tenía datos estáticos, no pude usar su medida real y utilicé CALCULATE ( ) para obtener ese valor, pero debe reemplazar CALCULATE( ) por [DTD PL].

Running Max =
VAR __thisdate =
    MIN ( FactData[REPORTDATE] )
VAR __last10days =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( FactData ),
                FactData[REPORTDATE] <= __thisdate
                    && FactData[REPORTDATE] >= __thisdate - 9
            ),
            FactData[REPORTDATE],
            FactData[PORTFOLIO]
        ),
        "@dtdpl", CALCULATE ( MAX ( FactData[DTD PL] ) )  //replace CALCULATE expression with your measure [DTD PL]
    )
RETURN
    MAXX ( __last10days, [@dtdpl] )

Si esto funciona para usted, márquelo como la solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita

[mjk1992]

Hola @mahoneypat,

Muchas gracias por la rápida respuesta. Sin embargo, probé esta fórmula y recibí un retorno muy grande en valor. No es lo que esperaba 😕
Así es como se ve el código:

Running Max = 
VAR __thisdate =
    MIN ( 'Model Table'[REPORTDATE] )
VAR __last10days =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Model Table' ),
                'Model Table'[REPORTDATE]<= __thisdate
                    && 'Model Table'[REPORTDATE] >= __thisdate - 9
            ),
            'Model Table'[REPORTDATE],
            'Model Table'[PORTFOLIONAME]
            
        ),
        "@dtdpl", MAX ( 'Model Table'[DTD PL] )   //replace CALCULATE expression with your measure [DTD PL]
    )
RETURN
    MAXX ( __last10days, [@dtdpl] )

Si esto diera la vuelta que quería, ¿cómo lo modificaría para que funcione desde una "tabla de fechas" separada con la que utilizo una relación con otras medidas?

Confirmé que esta solución no funciona como se incluye para usar tanto las fechas de la "tabla de fechas" como las fechas de la tabla del modelo.

Los valores devueltos se ven así, lo tengo en comparación con la DTD PL (tabla nativa a la tabla de hechos) y la medida YTD PL. El código que proporcionó se encuentra en la columna "Running Max".

Aquí hay un desglose en 6/1/2020 para una cartera. (ignore la columna PICO PL LTD)

Tengo una columna "LTD PL PEAK" trabajo en curso utilizando instrucciones IF de comunicación para aislar la respuesta, sin embargo está inacabado (de ahí el 0). Creo que tendría que iterar 9 bucles más para obtener la respuesta, pero esto se volverá bastante agitado una vez que usemos esto para ventanas de 45 - 90 días.
Aquí está el código para que si usted quiere ver hehe

LTD PL PEAK = 
VAR LTD10DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -10
    )
)
VAR LTD9DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -9
    )
)
VAR LTD8DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -8
    )
)
VAR LTD7DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -7
    )
)
VAR LTD6DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -6
    )
)
VAR LTD5DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -5
    )
)
VAR LTD4DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -4
    )
)
VAR LTD3DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -3
    )
)
VAR LTD2DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -2
    )
)
VAR LTD1DayPrior = CALCULATE (
    SUM ( 'Model Table'[DTD PL] ),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE] ),
        'Date Table'[REPORTDATE] <= MAX ( 'Date Table'[REPORTDATE]  ) -1
    )
)
VAR LTDPL =  CALCULATE (SUM(
    'Model Table'[DTD PL]),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE]),
        'Date Table'[REPORTDATE] <= MAX ( ( 'Date Table'[REPORTDATE]) )))


RETURN
IF(
LTD10DayPrior<=LTDPL && LTD9DayPrior<=LTDPL && LTD8DayPrior<=LTDPL && LTD7DayPrior<=LTDPL && LTD6DayPrior<=LTDPL && LTD5DayPrior<=LTDPL && LTD4DayPrior<=LTDPL && LTD3DayPrior<=LTDPL && LTD2DayPrior<=LTDPL && LTD1DayPrior<=LTDPL,LTDPL,0)

[mahoneypat]

Que CALCULATE() es clave para que esto funcione (desencadena la transición de contexto para evitar obtener ese gran resultado). No estaba seguro de si DTD PL en su objeto visual era una columna o una medida. Las medidas ya se envuelven automáticamente en CALCULATE(). Desde su último post, parece que es una columna, así que por favor intente este pequeño cambio (que hará una gran diferencia).

Running Max = 
VAR __thisdate =
    MIN ( 'Model Table'[REPORTDATE] )
VAR __last10days =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Model Table' ),
                'Model Table'[REPORTDATE]<= __thisdate
                    && 'Model Table'[REPORTDATE] >= __thisdate - 9
            ),
            'Model Table'[REPORTDATE],
            'Model Table'[PORTFOLIONAME]
            
        ),
        "@dtdpl", CALCULATE( MAX ( 'Model Table'[DTD PL] ) ) 
    )
RETURN
    MAXX ( __last10days, [@dtdpl] )

Si esto funciona para usted, márquelo como la solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita

[mjk1992]

palmadita

Pude progresar aquí. Esto produjo el producto correcto para DTD PL con una ligera modificación.
Los cambios que hice fueron para hacer referencia a la tabla de fechas y resumidos por la fecha de esa tabla. También saqué el CALCULATE(MAX( y reemplaqué con un CALCULATE(SUM(. No estoy seguro de por qué esta lógica terminó funcionando, era una especie de enfoque de prueba/error.

DTD Running Max = 
VAR __thisdate =
    MIN ( 'Date Table'[REPORTDATE] )
VAR __last10days =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Date Table'),
                'Date Table'[REPORTDATE] <=__thisdate
                    && 'Date Table'[REPORTDATE]>= __thisdate - 9
            ),'Date Table'[REPORTDATE]
            
        ),
        "@dtdpl", CALCULATE( SUM( 'Model Table'[DTD PL] ) 
    ))
RETURN
    MAXX ( __last10days, [@dtdpl] )

Ahora que tenemos un DTD Running Max en funcionamiento, intenté crear el LTD Running Max replicando la lógica para el DTD Max y reemplazando el CALC(SUM([DTD PL] variable que es un calc(sum( con filtros para fechas, pero esta fórmula a continuación sólo produce un cálculo NORMAL LTD?

LTD Running Max = 
VAR __thisdate =
    MIN ( 'Date Table'[REPORTDATE] )

VAR LTDPL =     CALCULATE (SUM(
    'Model Table'[DTD PL]),
    FILTER (
        ALL ( 'Date Table'[REPORTDATE]),
        'Date Table'[REPORTDATE] <= MAX ( ( 'Date Table'[REPORTDATE]) )))

VAR __last10days =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Date Table'),
                'Date Table'[REPORTDATE] <=__thisdate
                    && 'Date Table'[REPORTDATE]>= __thisdate - 9
            ),'Date Table'[REPORTDATE]
            
        ),
        "@ltdpl", CALCULATE(LTDPL)
    )
RETURN
    MAXX ( __last10days, [@ltdpl] )

Esto es lo que las fórmulas se ven en la tabla a partir de ahora.


Gracias por su continuo apoyo! ¡Siento que ya casi llegamos!

[mahoneypat]

No estoy seguro de por qué el último número es incorrecto, pero para la medida LTD, no se pueden utilizar variables como esa. Simplemente lo calculará una vez, por lo que su medida no es dinámica. Debe colocar la expresión CALCULATE correcta en la columna [@dpdpl] y debería funcionar.

saludos

palmadita

[mjk1992]

Acabo de notar que el último día, 7/1/2020 en el DTD Running Max está dando una mala respuesta. Tal vez de vuelta al tablero de dibujo en ese también. Caray.