Columnas pivotadas que cambian de nombre cada año, ¿hay una fórmula para hacer referencia a ellas?

[Anonymous]

Hola, tengo un origen de datos con datos en filas y quiero ponerlos en colum para hacer fórmulas entre las columnas.

Así que se ve algo como esto:

Producto	año	Ventas
a	2018	25
a	2019	264
a	2020	651
b	2018	810
b	2019	245
b	2020	369

Así que lo que hago es pivotar la columna en la consulta de energía como esta:

Producto	2018	2019	2020
a	25	264	651
b	810	245	369
Grand Total	835	509	1020

Luego hago fórmulas como la varianza a través de la columna mediante la creación de medida, etc.

Como:

Desviación (sum(2019) - sum(2018) )

varianza% - iferror( [varianza] / suma(2018) , "")

Mi problema es que el próximo año la columna habrá cambiado de 2018, 2019 y 2020 a 2019, 2020 y 2021. ¿Hay alguna manera de hacer referencia a estas columnas dinámicas pivotadas para que no tenga que rehacer las fórmulas (medida) cada año?

[Icey]

Hola @vincent2369 ,

Si te entiendo correctamente, creo que no necesitas cambiar tu estructura de datos.

Por favor, intente algo como:

1. Crear medidas:

variance = 
VAR ThisYear =
    MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR ThisYearSum =
    SUM ( 'Table'[sales] )
VAR LastYearSum =
    CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
    ThisYearSum - LastYearSum

variance% = 
VAR ThisYear =
    MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR LastYearSum =
    CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
    IFERROR ( [variance] / LastYearSum, BLANK () )

2. Coloque las medidas anteriores en un objeto visual de tabla o en un objeto visual Matrix.

Para obtener más información, compruebe el archivo PBIX adjunto.

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Anonymous]

@Icey ¿Hay alguna manera de mostrar 0 o en blanco para la varianza en 2018?

[Icey]

Hola @vincent2369 ,

Prueba esto:

variance = 
VAR ThisYear =
    MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR ThisYearSum =
    SUM ( 'Table'[sales] )
VAR LastYearSum =
    CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
    IF (
        ThisYear = MINX ( ALLSELECTED ( 'Table' ), 'Table'[year] ),
        BLANK (),
        ThisYearSum - LastYearSum
    )

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Anonymous]

¡Funciona, gracias!

[Anonymous]

Estoy seguro de que todas las soluciones funcionan, pero @Icey solución es la más fácil de implementar!

[amitchandak]

Puede crear medidas datesytd o totalytd o trailing con Calendario de fechas

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Para obtener lo mejor de la función de inteligencia de tiempo. Asegúrese de que tiene un calendario de fechas y se ha marcado como la fecha en la vista de modelo. Además, únase a él con la columna de fecha de su hecho/s. Consulte:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[edhans]

No en DAX que soy consciente de. Sin embargo, se realiza fácilmente en Power Query. A continuación, solo tiene que hacer referencia a estos nombres de columna fijos en DAX.

Pegue este blob de código M en una nueva consulta en blanco en Power Query mediante el Editor avanzado.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMLQAUaZKsTpwEUsQZWaCJGRkAKTMTA3BQkkIfRaGBshCYI0mpkhCYI3GZpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product = _t, year = _t, sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product", type text}, {"year", Int64.Type}, {"sales", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"year", type text}}, "en-US")[year]), "year", "sales", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{
        {Table.ColumnNames(#"Pivoted Column"){1}, "2 Yr Ago"},
        {Table.ColumnNames(#"Pivoted Column"){2}, "1 Yr Ago"},
        {Table.ColumnNames(#"Pivoted Column"){3}, "Current Year"}
        })
in
    #"Renamed Columns"

La función Table.Columns("Pivoted Column") -n está recibiendo el nombre de la columna Nth (índices de Power Query en 0) y renombrándola a lo que desee. Se ve así: