I have a dataset with 3 rows each row data will be used for next 6 months as is.
transaction id, date, amount
1, 1/1/2022, 100.00
1, 7/1/2022, 50.00
1, 1/1/2023, -150.00
I want the pivot table to populate the transaction amount for January 2022 through June 2022 as 100$ . Then from July through December 2022 as 100+50=150$
Basically I will not have the data for 5 months as it will be the same but it will change after the 6th month. Is it possible to achieve this using Pivot table in QuickSight?
My final output should look something similar to this-