Pivoted columns that change name every year, is there a formula to reference to them?

[Anonymous]

Hello, I have a data source with data in rows and I want to put them in colum to do formulas between the columns.

So it looks something like this:

product	year	sales
a	2018	25
a	2019	264
a	2020	651
b	2018	810
b	2019	245
b	2020	369

So what I do is I pivot the column in power query like this:

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

Then I do formulas like variance accross the column by creating measure, etc.

Like:

variance = (sum(2019) - sum(2018) )

variance% = iferror( [variance] / sum(2018) , "")

My problem is that next year the column will have changed from 2018, 2019 and 2020 to 2019, 2020 and 2021. Is there a way to reference to these dynamic pivoted column so I don't have to redo the formulas (measure) every year?

[Icey · Accepted solution]

Hi @Anonymous ,

If I understand you correctly, I think you don't need to change your data structure.

Please try something like so:

1. Create Measures:

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. Put the Measures above in a table visual or a Matrix visual.

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey  Is there a way to show 0 or blank for variance in 2018?

[Icey]

Hi @Anonymous ,

Try this:

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
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It works, thank you!

[Anonymous]

I'm sure all the solutions work but @Icey solution is the easiest to implement!

[amitchandak]

You can create datesytd or totalytd or trailing measure with Date calendar

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
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]

Not in DAX that I am aware of. However, easily done in Power Query. Then you just reference these fixed column names in DAX.

Paste this blob of M code in a blank new query in Power Query using the Advanced Editor.

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"

The function Table.Columns(#"Pivoted Column"){n} is getting the name of the Nth column (Power Query indexes at 0) and renaming it to whatever you want. It looks like this: