Forum Discussion

Bebs's avatar
Bebs
Icon for Helper II rankHelper II
4 years ago
Solved

Adding dynamic versus columns in a matrix table

 

Hi,

a customer ask me to build a table with vs columns in the table :
the full table will be about Sales, in row we have a country field.
the customer wants that kind of columns :

2020, 2021, 2022, 2021 vs 2020, 2022 vs 2021

(2020 means this columns should contains Sales of year 2020, 2021 vs 2020 means this columns should contains Sales of year 2021 - Sales of Year 2020) .

 


How can I build this table ?

I manage to do this by using static measures by Column :

for example for column 2020, I create the following measure for the column :
Sales_2020=CALCULATE(Sales[Sales], sales_table[YEAR]=2020)

then for 2021vs2020 I create the following measure for the column :
Sales_2021_2020 = [Sales_2021]-[Sales_2020]

It works, but it is not dynamic enough :

- Customers would also like to change the indicator in the table (uses expenses instead of Sales for example).

- Years will change (we can add 2019 data for example, or next Year we will have 2023 data) and the table should adapt automatically (adding columns or at least changing year in columns).

- (bonus) same questions with % diffence (exemple % difference between 2021 and 2020).


Do you have some ideas on how to handle that, the most dynamic way ?


Best regards

2 Replies