Pivot table creating dummy transactions with standard opening balance


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-

I believe this is related to Opening Balances

To elaborate a bit more on having entries for months without any records, you need to LEFT JOIN your existing dataset with a table containing all dates - so the the resultant dataset will also contain all dates. This is the only way to achieve this with Pivot Tables since these do not support populating missing dates automatically. (Line charts should support the option to fill in date gaps).