Microsoft Power BI y DAX: cambio porcentual de cómputo entre los números de semana de PY - medidas no YTD

[Syndicate_Admin]

Tengo un escenario en un objeto visual de Power BI Matrix, donde tengo una medida simple, llamada SalesAmount. Es una suma simple basada en una sola columna de una tabla, llamada FactSales.

Tengo una tabla de dimensión de fecha (calendario) en mi modelo denominada Dates2.

He desarrollado un informe matricial como se muestra a continuación. Este informe utiliza un WeekNumber de la tabla Dates2. También he desarrollado otra medida llamada SalesAmount_SWLY obtener la medida SalesAmount para la misma semana del año anterior. Mi ciclo de número de semana es de lunes a domingo (no el ciclo predeterminado de domingo a sábado).

(El primero de los 2 elementos visuales anteriores es el informe real, el segundo es solo para mostrarle cómo se almacena la medida para la semana correspondiente del año anterior).

Es importante tener en cuenta que la medida, SalesAmount NO es acumulativa (no YTD), es decir, para la Semana-10 de un año, NO necesitamos agregar la Semana-1 a las ventas de la Semana-10, sino mostrar solo las ventas de la Semana-10. Pero los totales del año se agregan a lo largo de todas las semanas para un año determinado.

Luego desarrollo una medida para generar el cambio porcentual, en semanas paralelas. Por ejemplo, la Semana-1 de 2020 se compara con la Semana-1 de 2019, para desarrollar el % de cambio.

Fórmula para el % de cambio en la semana 1 de 2020 = {(Valor en la semana 1 de 2020) - (Valor en la semana 1 de 2019)}/ (Valor en la semana 1 de 2019)

Fórmula para % de cambio en 2020 = {(Total de todas las semanas de 2020) - (Total de todas las semanas de 2019)}/ (Total de todas las semanas de 2019)

Esto funciona bien, pero por un tema. En el año 2021, tenemos datos solo para 2 semanas. (Supongamos que a partir de hoy, hemos completado solo 2 semanas en 2021, a pesar de que estamos en septiembre)

En lo que respecta al cambio porcentual, para el total agregado de 2021 (la última columna de la matriz en el lado derecho), debemos comparar el total agregado de las primeras 2 semanas de 2021, con el total correspondiente de las dos primeras semanas de 2020.

En la imagen de arriba, no quiero ver -95.66%, sino -18.75%.

Lógica: solo hemos completado dos semanas en 2021

Valor agregado en 2021 = 13 (es decir, 6 + 7)

Valor agregado en 2020 = 16 (es decir, 1 + 15, solo para las dos primeras semanas en 2020)

(Actualmente el valor de 2020 se toma como 300, lo cual es incorrecto)

Variación porcentual = (13-16)/16 = -18,75 % (necesario en el informe)

Variación porcentual = (13-300)/300 = -95,66 % (actualmente se muestra en el informe, incorrecto)

Publiqué una pregunta similar hace unos meses, para el cambio porcentual a través de los meses a través de diferentes años:

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-DAX-Time-Intelligence-measure-reflect-pr...

¿Puede alguien ayudarme a resolver este problema de cambio porcentual en los números de la semana, utilizando una nueva medida en DAX?

Lógica:

1. Estoy usando el ciclo de 7 días de lunes a domingo, para calcular los números de la semana en la tabla Dates2. El nombre de la columna es WeekNumber en la tabla Dates2. Calculo la medida para el mismo número de semana del año pasado, utilizando la siguiente fórmula:

   SalesAmount_SWLY = CALCULAR(

   [SalesAmount],

   FILTRO(

   ALL(Fechas2),

   Dates2[Año] = SELECTEDVALUE(Dates2[Año]) - 1
   &&
   Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])

   )

   )

2. Estoy bien en la forma en que calculo el cambio porcentual entre semanas individuales de diferentes años, digamos la Semana 5 de 2020 y la Semana 5 de 2019. De manera similar, los números de semana correspondientes de dos años consecutivos cualesquiera.

3. Tengo un desafío al calcular el cambio porcentual anual (grande) entre el año más reciente (2021) y el año anterior (2020) (esto se debe a que, en 2021, no hemos completado todas las semanas)

4. Solo tengo 'n' semanas en 2021. El gran total para 2021 en todas las 'n' semanas está bien; n < 53 (en su mayoría, hasta que se complete el año completo).

5. Voy al año anterior 2020. Encuentra la fecha del 7º día de la enésima semana de 2020. Permítanme llamar a esta fecha X. Esta se considera la fecha de finalización del año anterior (2020).

6. El gran total para 2020, se calcula desde el 1 de enero hasta la fecha X. Este valor se utiliza para calcular el cambio porcentual anual de 2020 a 2021.

Para el cambio porcentual anual (grandioso) entre años consecutivos, necesito tomar el gran total para ambos años, solo cuando ambos años tienen 53 semanas de datos. Para el año más reciente (digamos 2021), la mayoría de las veces no tendré todas las semanas. En este caso, entre 2021 y 2020 (solamente), comparo los datos solo para las primeras 'n' semanas. Para todos los demás casos, las cosas son normales.

[Syndicate_Admin]

¿Alguna ayuda? ¿Alguien puede sugerir algo? Si necesita claridad en la pregunta, no dude en preguntarme.

[Syndicate_Admin]

Hay @snph1777

Creo que encontró dificultades para calcular el precentaje en 2021 y 2020 en Parte Total en su matriz.

Aquí construyo una muestra para tener una prueba. Mi muestra tiene el mismo valor en Week1, Week2, Week3 y Week53 que se muestra en la captura de pantalla. Si mi medida es correcta, mostrará un 18,75% en el total de 2021. (Tengo semana53 en 2020)

Mis fechas2 son las siguientes.

Dates2 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "WeekNo", WEEKNUM ( [Date], 2 ),
    "WeekNum",
        "Week" & "-"
            & WEEKNUM ( [Date], 2 )
)

Relación:

La medida es la siguiente.

SalesAmount = SUM('Table'[Sales])

SalesAmount_SWLY = 
VAR _LastNotblankDateinSales =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] ) = MAX ( Dates2[Year] )
                && 'Table'[Sales] <> BLANK ()
        ),
        'Table'[Date]
    )
VAR _MaxWeekNum =
    WEEKNUM ( _LastNotblankDateinSales, 2 )
VAR _LastYearBody =
    SUMX (
        FILTER (
            ALL ( Dates2 ),
            Dates2[Year]
                = SELECTEDVALUE ( Dates2[Year] ) - 1
                && Dates2[WeekNum] = SELECTEDVALUE ( Dates2[WeekNum] )
        ),
        [SalesAmount]
    )
VAR _LastYearTotal =
    SUMX (
        FILTER (
            ALL ( Dates2 ),
            Dates2[Year]
                = SELECTEDVALUE ( Dates2[Year] ) - 1
                && Dates2[WeekNo] <= _MaxWeekNum
        ),
        [SalesAmount]
    )
RETURN
    IF ( HASONEVALUE ( Dates2[WeekNum] ), _LastYearBody, _LastYearTotal )

Percentage = DIVIDE([SalesAmount]-[SalesAmount_SWLY],[SalesAmount_SWLY])

El resultado es el siguiente.

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

@RicoZhou muchas gracias; Probaré su solución y volveré; agradecemos su ayuda

[Syndicate_Admin]

@RicoZhou

¿Funcionará esto, cuando no tenga 53 semanas en ningún año anterior (que no sea 2021)?

Digamos que 2016, tengo las 53 semanas, pero en 2017 solo tengo las primeras 50 semanas (no hay datos en la semana 51 o la semana 52 o la semana 53), porque no se registraron datos para las últimas 3 semanas de 2017.

Cuando hago la comparación de % entre 2017 y 2016, en un caso como este, necesito el gran total de la matriz para 2017 para usar todas las semanas de ambos años, y NO parar con solo 50 semanas para 2016.

En esencia, necesito esta lógica de reducir las semanas para el año anterior, SOLO para el año en curso, es decir, 2021, y cuando aún no hayamos completado todas las 53 semanas del año en curso.

[Syndicate_Admin · Accepted solution]

Hay @snph1777

Creo que necesita actualizar su SalesAmount_SWLY Measure.

SalesAmount_SWLY = 
VAR _LastYear =
    YEAR ( MAXX ( ALL ( 'Table' ), 'Table'[Date] ) )
VAR _LastNotblankDateinSales =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] ) = MAX ( Dates2[Year] )
                && 'Table'[Sales] <> BLANK ()
        ),
        'Table'[Date]
    )
VAR _MaxWeekNum =
    IF (
        MAX ( Dates2[Year] ) = _LastYear,
        WEEKNUM ( _LastNotblankDateinSales, 2 ),
        53
    )
VAR _LastYearBody =
    SUMX (
        FILTER (
            ALL ( Dates2 ),
            Dates2[Year]
                = SELECTEDVALUE ( Dates2[Year] ) - 1
                && Dates2[WeekNum] = SELECTEDVALUE ( Dates2[WeekNum] )
        ),
        [SalesAmount]
    )
VAR _LastYearTotal =
    SUMX (
        FILTER (
            ALL ( Dates2 ),
            Dates2[Year]
                = SELECTEDVALUE ( Dates2[Year] ) - 1
                && Dates2[WeekNo] <= _MaxWeekNum
        ),
        [SalesAmount]
    )
RETURN
    IF ( HASONEVALUE ( Dates2[WeekNum] ), _LastYearBody, _LastYearTotal )

En mi muestra, el total en 2016 es 15, y 2017 termina en la "Semana 50". El porcentaje total en 2017 debería ser (22-15)/15 = 46,67%

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

Seguro que lo haré; Ciertamente finalizaré esto, en uno o dos días, esta semana con seguridad. Estoy ayudando en algún otro trabajo, pero me centraré en este a partir del martes o miércoles.

[Syndicate_Admin]

Hay @snph1777

¿Podría decirme si su problema ha sido resuelto? Si es así, por favor acéptalo como la solución. Más personas se beneficiarán de ello. O todavía está confundido al respecto, por favor proporcióneme más detalles acerca de su tabla y su problema o compártame con su archivo pbix.

Saludos

Rico Zhou

[Syndicate_Admin]

@RicoZhou

Muchas gracias por su ayuda. Apreciarlo.

Cuando agrego algunas segmentaciones de datos para filtrar datos, no veo el valor correcto en la matriz de % de cambio.

Estoy de acuerdo con los valores individuales de la semana actual y la misma semana del año pasado, así como con sus totales.

¿Hay algún cambio de contexto que se realice cuando filtro datos usando segmentaciones de datos?

[Syndicate_Admin]

@RicoZhou

En lugar de usar SUMX en las variables, ¿podemos usar CALCULATE? ¿Cambiará eso el resultado? Me siento más cómodo usando CALCULATE, en lugar de SUMX y una medida dentro de él?

Tengo una medida DAX limpia a continuación, utilizando la lógica proporcionada por @RicoZhou

[Syndicate_Admin]

SalesAmount_SWLY = 

                   VAR LastYear = YEAR(

                                        MAXX(
                                             ALL(FactSales),
                                             FactSales[Date]
                                            )

                                      )


                   VAR LastDateOfRecordedSales = MAXX(

                                                       FILTER(

                                                               ALL(FactSales),

                                                               YEAR(FactSales[Date]) = MAX(Dates2[Year])
                                                               &&
                                                               ISBLANK(FactSales[Sales]) = FALSE

                                                              ),

                                                        FactSales[Date]

                                                       )


                   VAR MaxWeekNumber = IF(
                                     
                                           MAX(Dates2[Year]) = LastYear,

                                           WEEKNUM(LastDateOfRecordedSales, 2),

                                           53

                                          )


                   VAR SWLY = CALCULATE(

                                          [SalesAmount], 

                                          FILTER(

                                                  ALL(Dates2),

                                                  Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
                                                  &&
                                                  Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])

                                                )

                                       )


                   VAR SWLYtotal = CALCULATE(

                                               [SalesAmount], 

                                               FILTER(

                                                       ALL(Dates2),

                                                       Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
                                                       &&
                                                       Dates2[WeekNumber] <= MaxWeekNumber

                                                     )

                                            )


                   RETURN

                          IF(

                              (HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = TRUE),

                              SWLY,

                              IF(

                                 (HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = FALSE),

                                 SWLYtotal,

                                 BLANK()

                                )

                             )

[Syndicate_Admin]

@RicoZhou

Volveré en las próximas 24-48 horas. Perdón por el retraso. (Me han retrasado en alguna otra tarea, pero este problema de DAX sigue siendo una prioridad).