Opening Balances

Is there a way to project opening balances for each month in the future based on a single or multiple transaction records? For example, I have a record
CustomerID, TranDate, TranAmt
Cust01, 1/1/2021, 100
Cust01, 10/1/2021, 400

From these records, I want to report the closing balances every month in a pivot table for the next 12 months. My starting date can be dynamic and from the above example, my closing balance for each month from Jan 2021 to Sept 2021 is 100, from Oct 2021 to any date in the future closing balance for each month is 500.

I think here you need to solve 2 problems:

  1. Getting the closing balances This can be done using a running sum calculation
    runningSum - Amazon QuickSight
runningSum(sum({TranAmt}), [{TranDate} ASC], [])

You should put this field in your pivot table, together with {TranDate} (with Month Aggregation) to get the closing balances as at the end of each month.

  1. Filling missing dates. The above will only show the balances for months that actually have at least one record in the dataset. From your question, it is implied that all months between your start and end date need to be present irrespective if they appear in the dataset or not. The way to achieve this is to LEFT JOIN your existing dataset with a date (month) dimension table during the data preparation stage.