Percent of change on the last two pivoted columns

flaviagdo · ‎07-07-2021

Knowledge Drop

Last tested: May 1, 2020

In case we have a date column that is pivoted and want to calculate the percentage of change only on the last two columns. The main issue is that the last two columns are going to be dynamic as the users can select any time range filter (last 3, 6 months and so on).

In this example, the user is pivoting into Created Month column. So to get the last column we can use

pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))

And combine it with the previous column getting to the whole calculation as:

pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))/ pivot_index(${pivotted_measure}, max(pivot_row(pivot_column()))-1)

This content is subject to limited support.