Filter data on last 12 months but excluding current month?

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!

Date Time

Accepted answers

jbichachi003

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

All comments

jbichachi003

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

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15873

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703