How to build a formula for knowing how much cash I have left to spend (Dynamic DateRange Filter)


I’m trying to build a tool to track my personal spendings and incomes. For that one of the things I want to know is how much cash I’m currently have left to spend (in total, not monthly).

I have a Expenses table and Incomes table were I regularly add the expenses with the date the payment was done. I do the same with Incomes, on a separate table. I also created formulas Start Date (The first day where the system started to run) and Today (“Today()”).

I thought of a possible solution to this dilema could be by creating filtered views of those tables, filtered by a date range between Start Date and Today, and then making a formula with a substraction between the sum of the values from the filtered Expenses table and the sum of the values from the filtered Incomes table.

I was unable to do this in code. Can someone help me with this?

Also, if you have a better approach to this solution please feel free to include it.

Thanks in advance!

Welcome to community @Juan_Martin_Lusiardo

Not entirely sure the need of date range filter here esp if you just want to know how much cash you have left irrespective of the month. if you have an expenses and incomes table separately - and lets assume Amount is the column on these tables - then Incomes.Amount.Sum() - Expenses.Amount.Sum() will give you the money you have left.

Thanks for the warm welcome @Krunal_Sheth :slightly_smiling_face:

I’m sorry for not being specific enough: The need of having a date range filter is because I use this tool to estimate how much money it will come the next month, or in 1 year, for example (based on my fixed expenses and what I expect I will typically generate for income).

I currently have expenses from Aug, Sep, Oct, Nov and Dec inputted on those tables, so if I go with that solution I won’t have the current amount of money I’m currently have today available to spend.

Dear @Juan_Martin_Lusiardo,

I recommend to have a look to this template, quite sure it will put you in the right direction

In case you face any other challange, I recommend to create a dummy doc with some basic info and your expectation what to happen. This will make it more attractive for the community to give you quick a helping hand :handshake: