Sum Column with Date Range

sam1968 · 10-05-2023 06:53 AM

I have a simple checkbook table with columns: key, date, item, and amount. The amount column has positive numbers for credits (deposits) and negative numbers for debits (withdrawals). Very straightforward.

My goal is to calculate the running balance up to today. So, to begin at the first date in the table, and calculate the current balance of values in the amount column, based on the range of dates: the date at the beginning of the register to today.

Again, if the first date was 1/1/2019, and the last date is 10/5/2023, I would like to sum all the amounts within that date range in the "amount" column. This (theoretically) should give me the running balance for the checking account.

I would like to display this number in a dashboard, side by side with the register table. Preferably it would be an idea visualization to have a date picker, where the two views in the dashboard (one view with running balance as of today, and another view as a table of register rows) relate and interact based on date selection.

I will provide screenshots if that helps. Thank you.

AleksiAlkio

You would need to have one additional normal column with an app formula something like..

SUM(SELECT(Table[Amount],[Date]<=TODAY()))+[Amount]

AleksiAlkio

Actually.. it would be better to write like this if it's possible that you modify the record later..

SUM(SELECT(Table[Amount],[Date]<=[_THISROW].[Date]))+[Amount]

sam1968

Brilliant. That makes sense. It flows like a proper checkbook now. I have other features for the table and app that I'm hoping to implement, such as future transactions and the views I touched on earlier. For now this is working very well. Kudos!

AleksiAlkio

You're welcome!