Best way to return results that are between two specific dates (dynamic, and fixed e.g. this calendar month, last calendar month, this annum)

I have a basic ‘transaction’ type DB, whenever a user actions a certain thing - I input:
user_id
org_id
number (integer)
date (datetime).

What I’d like to be able to do, is the following:

Return the number sum of ‘number’ for:

• this month
• last month
• this year
• dynamically between two dates of the users choosing

Perhaps this is one of those use cases for ‘views’ which I haven’t explored yet, however, I’m seemingly unable to do this within the query builder, and even repeating using a where or outputting to a set value so I can use formatters doesn’t really give me the option, either.

I have an idea that I could somehow pass the appropriate dates from the F/E to the B/E - which is what I’m exploring now. The date formatters on SC just don’t seem like they are able to handle this natively, and so I might need a custom formatter of some kind?

I’d prefer to try and process this on the BE.