How can I define a date in an expression?

Get Help
1

I find a great number of functions to add, subtract, decypher and do other stuff with dates.
But I cannot find any way to simply define a date myself.

I'm in need of calculating for 40.000 records an amount of revenue, based on how long a contract is already running. I want to calculate it till end of the previous quarter.
I wanted to do this quickly for now & do fundamental work later.

So my line of thought was:

  • define custom col #1: EndOfQuarter, i.e. 31 March 2024 (this is my issue yet, how do I get to this date?)
  • define custom col #2: dateDiff ( [EndOfQuarter] , [ContractStart] , "month" ) * MonthlyRent to calculate the running amount of rent due

So how do I define some specific date?
And if this line of thinking wouldn't work at all, what would be a doable alternative?

2

Easiest way is to create a date dimension in your database. (for SQL Server, this is a good option: Creating a date dimension or calendar table in SQL Server). Very similar for other databases.

My normal approach is to never query the base tables but to create views instead. Then I can add my own date definitions within the views. That also means I'm not limited to just using Metabase.