Splitting data TY vs LY in matrix table

[dokat]

I have below P&L table on the left and i'd like to transpose and format it same way as the table on the righ. I created a matrix table Iand transpose data however it doesnt split the data between 2020 and 2021 . Is there a workaround splitting data between 2020 and 2021 and include as column header.

[AllisonKennedy · Accepted solution]

@dokat  The table on the left looks good from a database persepective, just add a column in Power Query that turns Calendar Year into a date (Jan 1, Calendar Year is easy). Then you can relate to a DimDate table and use TimeIntelligence.

Excel with Allison: DimDate: What, Why and How?

Alternatively you could just put Calendar Year in the Columns of your matrix and P&L in the rows and Values in Values and no transposing is needed.

[dokat]

that worked, i uploaded calendar year table and drop it under column  and it worked.

Thanks