[Calc] Pulling remaining balance at end of each month for Financial Analysis

[fintrack]

I have a financial tracking spreadsheet (.ODS) with column headers: DATE, AMOUNT, BALANCE (example attached below). I have a separate sheet within the same spreadsheet where I analyze the information. Included in that analysis is the remaining balance at the end of each month.

Goal: Pull and display the remaining balance at the end of each month

Problem:

1. There are multiple transactions a day (only the date is listed, not time)
2. Sometimes there is no transaction on the actual final day of the month.

Attempted methods:

1. I have tried XLOOKUP with the following formula XLOOKUP(MONTH END DATE,DATE RANGE,BALANCE RANGE,-1,-2). I have tried the different options for match mode and search mode but cannot get a consistently good result. This generally fails when there is no transaction on the last day of the month
2. I have tried a longer approach of finding the vertical indices of the date range → take the last/first index corresponding to the last transaction → take balance from that vertical index. I am running into trouble when I try to find the index in a table with a non-exact match (closest match).

Is there a cleaner approach to this?

My system:

Version: 25.2.7.2 (X86_64) / LibreOffice Community

Build ID: 5cbfd1ab6520636bb5f7b99185aa69bd7456825d

CPU threads: 12; OS: Windows 11 X86_64 (10.0 build 26200); UI render: Skia/Raster; VCL: win

Locale: en-US (en_US); UI: en-US

Calc: CL threaded

Windows 11 Pro

Financial_Tracking_Example.ods (78.8 KB)

[EarnestAl]

The pivot table is by far the easiest.
.
You could try looking for the start of the following month and go backwards but you will have to wait until the following month is entered.
Financial_Tracking_Example134545EA.ods (64.8 KB)

[karolus]

• You’ll need a Pivottable which aggregates the Amounts by sum ⇒Data⇒Pivottable⇒create…
• you’ll need to group the Date-Column of the Pivottable by Month and Year
  ⇒Data⇒Group and Outline⇒Group⇓

• and you’ll need a calculated «Balance per Month» right of the Pivot

ask134545_pivot_grouped_plus_calculated_balance.ods (69.0 KB)