I receive data for a list of balances from year 2020 to year 2050. The data is in the format of the first table below. For each year of data, I am adding 3 additional calculated custom columns in PQ as shown in the second table. The formulas are the same for all years. The custom column formulas for the first year are working, but it will be a tedious and time consuming undertaking to replicate them for the other 30 years. I was wondering if there are relative column references that I could use so the same formula can be used for all years without writing a custom column function for each column and explicitly referencing the columns.
My data source looks like this
It looks like this after adding the 3 calculated columns, but they need to be added for all years.
My data source looks like this
| Name | Year 2020 | Year 2021 | Year 2022 |
| Item 1 | 10 | 14 | -18 |
| Item 2 | -12 | 16 | 20 |
It looks like this after adding the 3 calculated columns, but they need to be added for all years.
| Name | Year 2020 | 2020 (Calc 1) | 2020 (Calc 2) | 2020 (Calc 3) |
| Item 1 | 10 | If[Year 2020}<0 then 1 else 2 | if 2020 calc1 > 2 then year 2020balance else 2020 calc3 | if 2020 calc 2 > year 2020 balance then 1 else 2 |
| Item 2 | -12 |
