We load sales results monthly, and sometimes these come in the form of groups of weekly files.
This is leaving me with an issue for my month on month growth figures.
Ie - today it is comparing October 1-3 against September 1-3,
Whereas what I want it to show is September growth over August as these are the last 2 complete months I have.
Currently I'm using :
to give me what should be last month.
---Variable for Max Month is a pretty standard : =Month(Max(invoice_date))
For the prior month., where ---- month(addmonths(max(invoice_date) ,- 1)) is my vPriorMonth
Does anyone know how I can ignore those months that are showing dates less than ...let's say the 26th?
Thanks in advance
Thanks, This is throwing out August for me.
I did modify it to
month(addmonths(if( match(max(invoice_date) , monthend(max(invoice_date))), max(invoice_date), addmonths(max(invoice_date),-1)),0))
And then it gives me September.
The problem persists when a month is actually chosen though.
If - for example, I chose April (which is complete) then these expressions give me February and March respectively, which is not what I'm after.
I guess it's due to your invoice_date. how is your calendar programmed ? Full date range for every month? Then replace invoice_date with the key date in the calendar.
edit: this is the formula for your prev Month Variable. For your max Month Variable use:
month(if( match(max(invoice_date) , monthend(max(invoice_date))), max(invoice_date), addmonths(max(invoice_date),-1)))
Change the date to your calendar date field.
Hope this helps