Date calculations on full months

[Helmi]

Hey,

for our little CRM I’m trying to builds something based on customer revenue based on time. Like sales generated within the last 12 months.

I don’t want to calculate that on a daily basis but only check once a month on the last 12 months. I do have all the basic calcs setup but I’m unsure about the way on how to only look at full months. I thought about calculating back from today to the last day of the last month but didn’t find a way yet.

Any idea?

What I currently do is

IF(DATETIME_DIFF(Orderdate, NOW(),'d') > -365, Ordervalue, 0)

in the order table and then roll it up in the customer table.

Appreciate any idea on that.

[Jeremy_Oglesby]

So you are currently rolling up a sliding 365-day window.
What you want is to roll up a sliding 12-month window that stops on the first day of the 12th month back? Is that right? So, for example, if TODAY() is 12/15/2018, you want to Rollup revenue back to 1/1/2018, as opposed to rolling up revenue back to 12/15/2017?