Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

So there is no confusion beforehand, here is a look at my setup:


 

Account_c from Delta Account and Related_Account_c from Client Revenue were appended into a single table as the unique ID so I can work with fields from both tables. Cross filters on both tables are set to 'Both'. If I do not set these to 'Both', I don't get individual revenue for each account like I'm looking for:

Wrong (single cross filter):

 

Correct (Both cross filter):

Now, to the formula. I am looking to build a formula based upon the revenue date that looks back two months from the current month, gathers the sum of revenue for that month (based on Revenue_Date_c) and multiplies that number by 12. So in plain terms since it is March, it would be:

(Sum of January Revenue) *12

 

Once it is April it will then be:

(Sum of February Revenue) *12

 

etc. etc.

 

So looking at one company for example:



(Working with December revenue until database is updated to bring in January numbers) The sum of revenue for December would be 71.28 (271.28-200.00) and multiplying that by 12 is 855.36 which is the result I'm looking for. How can I create a formula that gives this?

 

 

 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Well, you can get your EndDate for your calculation like this:

 

EndDate = EOMONTH(EDATE(TODAY(),-2),0)

And your StartDate like this:

 

StartDate = EOMONTH(EDATE([EndDate],-1),0)+1

So, you could use these as your filters to calculate 2 months ago from whatever TODAY is and then multiply by 12.

 

So, what is going on is that you are going back 2 months from today and then getting the end of the month for that month (EndDate). StartDate takes that date, subtracts a month, finds the end of that month and then adds 1 day to give you the start of the month of EndMonth. Lots of different ways of calculating this but this method ensures that you can span years without a problem.