Formula for date-based dynamic chart over unknown number of rows

I've been working on a spreadsheet for my personal finances. I have two complete years of transactions over four accounts. I wanted to graph the trends of each account and the sum of all accounts, but I only wanted to visualize the past year (365 days) of data.

I set out to create a dynamic chart and found a variety of resources online; one example was a dynamic chart for the past 12 months (12 entries) - all the examples I could find relied on a set number of data points. I have an unknown (or dynamic) number of daily transactions and the balance of my accounts after each such transaction. I managed to create a formula that gives me an offset based on the number of transactions over a set time period (365 days) rather than a set number of transactions:

Column A contains the date (YMD) of each transaction, column J contains the balance after the transaction.

Please Login or Register  to view this content.

Those formulas are the same, save for the column offset "9" for the balance. For the row offset, I find the last row of data using COUNTA() (this is reliable because I'll never have a transaction entry without a date) and subtract from that the number of transactions (COUNTIF) that occurred later than 365 days prior to the latest transaction (MAX). I reuse the COUNTIF/MAX to create the range by specifying the OFFSET height parameter as the number of transactions later than 365 days prior to the latest transaction.

I created a named range for each formula, created my chart with the date and balance columns, then updated the data series using the named ranges for the X and Y values.

I now have a rolling chart for all transactions within the past year of my latest entry.

Hope someone else finds this useful!