Help

Formula to calculate Actuals as at end of most recent quarter

Topic Labels: Formulas
86 2
cancel
Showing results for 
Search instead for 
Did you mean: 
KateR1
4 - Data Explorer
4 - Data Explorer

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!

2 Replies 2
ibayub
5 - Automation Enthusiast
5 - Automation Enthusiast

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")
 
ibayub_0-1738614172047.png

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

ibayub_1-1738614298386.png

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

 

ibayub_2-1738614376239.png
-----------------

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

Alexey_Gusev
13 - Mars
13 - Mars

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..

Alexey_Gusev_0-1738630236869.png

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')