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|
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.
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.
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]