Question

I am trying to make a financial dashboard. I have currently 4 databases. Accounts, Expense, Income and Budget. I am trying to display monthly income in my accounts table using rollup but i keep getting the sum of current and past months. Any advice on how to solve this?

Answer 1

You need to filter your view by month, there is no need to use a Rollup for that.

That way you will just see by month everything and at the bottom you can use the calculate options to see the totals.

Answer 2

Plain rollups can't filter by date, so you get the sum of every month. A formula on the Accounts database can filter the relation instead. Assume the relation to Income is called "Income" and has Date and Amount:

prop("Income")
  .filter(formatDate(current.prop("Date"), "YYYY-MM") == formatDate(now(), "YYYY-MM"))
  .map(current.prop("Amount"))
  .sum()

Name it Income this month. Make the same formula for Expense, and Net = Income this month − Expense this month.

For past months, add a Date property called "Month" to the account (or the dashboard row) and compare it against that instead of now(). Another option is a Months database (one row per month) related to each transaction, where a plain rollup Sum works per month.

For a quick visual, a chart view on Income grouped by Date (month) also works.

Free template with the client and project side of a freelance setup: https://2rds.github.io/freelancer-ops/lite.html?ref=notionanswers