Skip to main content

Hi,

Hoping that someone might be able to help me with a formula question (I'm quite new to formulas and have been fumbling along until now, but have reached the limit of my knowledge!)...

I have a table of payments that have been made, each with the payment date. These Payments are associated back to a project budget and are rolled up so that, at any point, you can see the total Actual spend to date. I've got this all working well.

I am now trying to add in a new field in the Project table which shows total Actual spend as at the most recent quarter end - and I'm stuck! I've managed to write a formula in the Payments table which formats the payment date into the year and quarter (so turning 2022-02-04 into 2022-1) but I now want to work out how to pull this through to the Projects table. I know that I could build a series of fields in the Projects table that rolled up (respectively) all Payments in 2022-1 and then all Payments 2022-2 etc etc, but this doesn't feel very sustainable as I will need to keep adding fields every 3 months. Is there a way to build a formula in the Projects table which works out what the current quarter is, and then pulls in the total actuals as of the end of the previous quarter?

Thanks in advance for any help!

Here's one approach that might work - 

1. In Payments, add a formula to check if the date is in current quarter and format the output as single select options (Yes/No). e.g. 

IF(Quarter = DATETIME_FORMAT(TODAY(), 'YYYY-Q'),"Yes","No")
 

2. In Projects, create a new rollup "Total this Quarter" that only sums the payments where isCurrentQuarter is Yes

3. Add a new column formula in Project that's Total Amount - Total this Quarter to get the total amount as of the end of the prev quarter

 

-----------------

if you dont want / need the total this quarter and/or you also have future payments, you could modify the formula in Payments to check all 3 cases (e.g. if payment quarter < current quarter, Past, if =, Current, else Future) too and then just roll up the past payments

Here is expanded formula, based on idea by @ibayub 
You can use it in rollup built-in filter, like one field shown total of current, next - total of previous quarter, etc..

Version to copy 

IF(DateField, IF(DATETIME_FORMAT({DateField},'YYYY-Q')=DATETIME_FORMAT(TODAY(),'YYYY-Q'),'Current Quarter', IF(DATETIME_FORMAT({DateField},'YYYY-Q')=DATETIME_FORMAT( DATEADD(TODAY(),-1,'quarter'),'YYYY-Q'),'Previous Quarter', IF(IS_AFTER((DateField),TODAY()),'Future','Past') ) ),'date absent')

Thankyou both so much, these are really helpful suggestions 🙂 We're just reconfiguring some fields in the Payments table, and once this is completed I will implement the suggestions above and will report back. Thankyou!

Thankyou both so much, these are really helpful suggestions 🙂 We're just reconfiguring some fields in the Payments table, and once this is completed I will implement the suggestions above and will report back. Thankyou!


Great good luck! 🙂 

Terms & ConditionsAccessibility statement