Forum Discussion
Post ProdigySplitting data TY vs LY in matrix table
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.
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.
2 Replies
- AllisonKennedy
Community Champion
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.
dokatthat worked, i uploaded calendar year table and drop it under column and it worked.
Thanks