How to create a calculated value and change it with a filter

[Martin Campos]

In one of the visualizations I have a table with a year column,

I made two columns calculated separating the year 2019 and 2020 so that it would be easier for me to calculate the variation in these two years, but when 2021 starts I will have to change all my calculated values because sum [2020] / sum [2019] -1

I want to know if there is a dynamic way to do this calculation, that I select my filter and change sum [2021] / sum [1020] -1

or

sum [2021] / sum [2019] -1

i want this part sum[2020] changes for sum[2021]

[Fabian Duerr]

I don't know your exact data structure but let's assume you only have two columns [YEAR] and [VAL].

Then you could use the following for your calculated value:

First(If([YEAR]=(Max([YEAR]) ),Sum([VAL]))) / First(If([YEAR]=Max([YEAR])-1,Sum([VAL])))

If you don't understand what this formular is doing than please evaluate both parts as a calculated columns. The aggregation First() is necessary to return a single value. But First() could be replaced by Min(), Max(), Median(), Last() as all values of If([YEAR]=(Max([YEAR]) ),Sum([VAL])) are identical.