Filter data on last 12 months but excluding current month?

[Crispy131]

Hello,

I want to filter my data so that only the last 12 months are displayed but I want to exclude the current month from showing too.

I currently have

[OpenDate] >= DateTimeFormat(DateTimeAdd(DateTimeToday(),-12,"months"), '%Y-%m-01')

E.g. If I run this in November 2022 it will give me data from 1st November 2021 till today's date but I want it to give me data from 1st November 2021 till 31st October 2022 (i.e. no November 2022 data because this month hasn't been completed yet)

Is there any way I can action this in the same formula? 

Any help is much appreciated! 

[jbichachi003 · Accepted solution]

Try this:
[OpenDate] >= DateTimeAdd(DateTimeFirstOfMonth(), -12, 'months')
AND [OpenDate] <= DateTimeAdd(DateTimeFirstOfMonth(), -1, 'days')

The DateTimeFirstOfMonth function will return date from the 1st of the current month. The top condition will return 12 months prior to the first 1st day of the current month (if we're in November 2022, it will return 1 November 2021), and the bottom condition will return one day prior to the 1st day of the current month (if we're in November 2022, it will return 31 October 2022).

[Crispy131]

Thanks! Just a little change- kept my first line as is (because otherwise 1st Nov 2021 was filtered out) and added your second AND condition. Worked great!

[jbichachi003]

Great, glad to help! If you found my post helpful, please mark it as the solution to close out this post. Thanks!

[sagarghimire]

1-Nov-2021 is still included from the first statement, it has >= , what am I missing?

[jbichachi003]

The original response I wrote needed to be tweaked about as the OP pointed out. See attached for the modified formula.