Creating filter for previous months in the year

[rsapaull]

Effectively what I am trying to do is create a flag in the load script similar to using the InYearToDate function for a YTD option, however this only works if you are looking at the current year and I have graphs that show CY and prior years.

So for example, let's say it is May, I would like a filter pane where I can have everything with a date where the month is January, February, March or April to be selected, regardless of the year for one value (e.g. -1) and then everything else is another value.  The field needs to be dynamic and I can't simply create a month filter and select these manually.

I have used the below formula to do this to create year to date, but it doesn't work if the graph has multiple years.  Any ideas?

InYearToDate(Renewal_Date,(monthend(addmonths(Today(),-1))),0)*-1 as YTD

[srdheekonda]

If i understood your question correctly, Create a flag column by taking the month number from your date column as well as dynamic current date by checking following condition IF DateMonthNumber<Today's Month Number THEN 1 ELSE 0

As per above scenario -- In the Month of May -- Today's date month number will be 5 and all the dates from Jan to April fall less than 4 month number and it will group to 1 bucket.

One point to be noted is in case of incremental load we need to take care of previous days data loaded for closed month when we are moving to new month to avoid any issues with the flag. I hope this helps

[rsapaull · Accepted solution]

For anybody else looking at this post, the solution as an actual formula is as follows:

IF((Num(Month(Renewal_Date)))<(Num(Month(Today()))),1,0) as MTD,