How to calculate differences between rows in a matrix with custom aggregations
I have this matrix
As you can see, the rows are aggregated by months, quarters and years. The values in the matrix are about sales.
Now I need to calculate the difference between the sales in a month and its previous one. The same must be done for quarters and years.
I do some examples. I need to calculate the difference between
- Year 2021 and Year 2020
- Quarter2 2020 and Quarter1 2020
- Quarter4 2020 and Quarter1 2019
- February 2021 and Jenuary 2021
- Jenuary 2021 and December 2020
To make things more complicated 😞 User can filters data. So for example, he could decide to visualize in the matrix data just for 2015 and 2018, then excluding 2016 and 2017. In that case, I should consider 2014 as the previous row of 2018 and not 2017.
Any update on your requirement? You can also take a look at the following link to know how to use if statement checks current hierarchy level then you can write a formula with filter for these parameters range calculation.