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