How to calculate percent change between values in only in last two dates (columns) in pivot table

I am trying to find the % change between last two days in a pivot table while the pivot has data for more than two days shown in separate columns.

Here is the raw data:

Metric_Descr Report_Date Report_Period Metric_Value
Test_Metric1 9/7/24 Daily 10
Test_Metric1 9/8/24 Daily 20
Test_Metric1 9/9/24 Daily 30
Test_Metric1 9/10/24 Daily 40
Test_Metric1 9/11/24 Daily 15
Test_Metric1 9/12/24 Daily 25
Test_Metric1 9/13/24 Daily 18
Test_Metric1 9/30/24 Monthly 158
Test_Metric2 9/7/24 Daily 15
Test_Metric2 9/8/24 Daily 44
Test_Metric2 9/9/24 Daily 23
Test_Metric2 9/10/24 Daily 56
Test_Metric2 9/11/24 Daily 343
Test_Metric2 9/12/24 Daily 32
Test_Metric2 9/13/24 Daily 56
Test_Metric2 9/30/24 Monthly 569

Here is pivoted data:

Metric_Description Sep 7, 2024 Sep 8, 2024 Sep 9, 2024 Sep 10, 2024 Sep 11, 2024 Sep 12, 2024 Sep 13, 2024
Test_Metric1 10 20 30 40 15 25 18
Test_Metric2 15 44 23 56 343 32 56

I want to find the % change between 9/12/2024 to 9/13/2024 for each row:(metric_description). Final table should look like:
Example dashboard link: Question & Answer - Amazon QuickSight Community

|Metric_Description|Sep 7, 2024|Sep 8, 2024|Sep 9, 2024|Sep 10, 2024|Sep 11, 2024|Sep 12, 2024|Sep 13, 2024|Day over day % change|

|ā€”|ā€”|ā€”|ā€”|ā€”|ā€”|ā€”|ā€”|ā€”|
|Test_Metric1|10|20|30|40|15|25|18|-39%|
|Test_Metric2|15|44|23|56|343|32|56|43%|

Hi @vpkn ,

Welcome to the QuickSight Community!

For your use case you use periodOverPeriodPercentDifference to calculate the needed metric.

Thanks,
Prantika

1 Like

Hi Prantika,

Thank you for responding. I have tried this function but it is giving the data for all columns (dates). I want to calculate the difference only between last dates and display in report. If you do not mind, can you experiment with my report.

Thanks

Can you reshare your arena dashboard link? The above link takes me back to the Q & A page.

Here is the link to arena:

Interestingly this link shows QA page thought it is copied from Quicksight:

Pivot_%Difference_Between_Last_Two_Date_Columns

Hi @vpkn ,

I do not think we can hide the rest column and only populate for the last date, considering you are looking at a pivot table.

In such a scenario, you overlay table against another table where you fetch only the delta for the last two dates.
But this is not a scalable solution, if you select higher number of day date, you may have a horizontal scroll.

Workaround 36432

Thanks,
Prantika

1 Like

Thank you. This approach works though the calculated metric appears detached from the main table.

BTW- This solution works only for continuous dates. This formula is not calculating the difference between Friday and Monday when we only contain weekdays in our data [and completely exclude weekend dates]

For a discontinued date data, you can explore using lead/lag to fetch following/previous date record.

Is there any option to skip the weekends while calculating the periodoverperioddifference?

1 Like

I do not think that is currently possible.
You can try percentDifference as well.

1 Like

Iā€™m looking for similar functionality