I have a custom object called jobs which are related to the master object accounts. Within the jobs object, I have a revenue field and an invoice_date field. On the accounts page layout, I want to display the total revenue for each related job per month of invoice for the current year and the previous year. I want the months/years to be dynamic so I, nor my predecessor, don't have to change them every year. I am currently doing this with the roll-up summaries, but, as I mentioned, I have to change them every year. I have established a lookup relationship within accounts with jobs, as a master-detail relationship doesn't seem to be enough, but cannot seem to get any kind of data from jobs. In addition, there doesn't seem to be a SUM method in formula fields.
Is there any way to sum all data from a specified field (revenue) filtered by another specified field (invoice_date) in a related object (jobs) within a formula field on an object (accounts)?
Ideally, the formula might look like this, except this doesn't work:
if(jobs__c.invoice_date >= DATE(YEAR(TODAY())-1,1,1) AND jobs__c.invoice_date <= DATE(YEAR(TODAY())-1,1,1), SUM(jobs__c.revenue), 0)