I've used hours to search for a solution via Airtable support pages, this community, Youtube (interesting video) and even ChatGPT, but I can't seem to find an out-of-the-box solution. Hopefully someone like you can help 🙂
In an Airtable dashboard interface my use case is just a 5 seconds configuration, and I can select it in a view's summary bar. But unfortunately not in the data tables as part of a formula so it seems.
The situation:
- I'm tracking expenses by importing transactions in a table {expenses} as separate records
- Each expense record has a {Date} field
- I'm categorizing each expense by selecting one category which is a linked field {Category} from the table {budget} with 73 category records.
What I'd like to do is create a formula in the {budget} table which uses - among other data - the latest date of all records in the {expenses} table. Why? To calculate the difference of my spending per category, which is now based on today instead of the last transaction date of all transactions via this formula for the field {Expenses Over(-) Under(+) €} in the {budget} table.
(
{Expenses Yearly (budget 2023)}
*
(DATETIME_DIFF({Latest Import Date},DATETIME_PARSE(DATETIME_FORMAT({Latest Import Date},'YYYY')&'-01-01'), 'days')/365)
)
-
{Spend to Date}
The MAX(values) Rollup function is not helpful here: in that case I get the latest date for each category based on the records linked with that specific category. But I want to use a function in a formula that takes into account all records in the table.
In the Dashboard interface it's easy to retrieve this latest date: see in screenshot right bottom:
Number > Field Summary > Field: Date > Summary Type: Latest Date
In the summary bar of a table view it's also easy to retrieve: see in screenschot:
Latest Date
Is there a way to accomplish this in the Data Table via an out-of-the-box solution?
Thanks in advance!