How to create a filter or segment expression that only displays the last "x" months

Example problem: I need a filter in my Visual or a Dataset Segment to filter my data to only show the last 2 months of data. For example, I want my filter range to be between July 1st, 2018 and August 31st, 2018, which would exclude any values from the current month since there’s only partial data.

Solution Part 1: Build the date transformation


to_date(date_sub(trunc(now(),‘month’), interval 2 months)),
to_date(date_sub(trunc(now(),‘month’), 1));


| ‘2018-09-13’ | ‘2018-07-01’ | ‘2018-08-31’ |
| 2018-09-13 | 2018-07-01 | 2018-08-31 |

Solution explained:

to_date(now()) - This is just for reference

to_date(date_sub(trunc(now(),‘month’), interval 2 months)) - Truncates the current date down to the first date of the current month and then subtracts 2 months from that date.

to_date(date_sub(trunc(now(),‘month’), 1)) - Truncates the current date down to the first date of the current month, and then subtracts one day to get the last day of the last month.

Solution Part 2: Build the filter expression

To build the filter expression we just take our date column (i.e. [Date]) and compare it between a start date (July 1st) and end date (August 31st).

[Date] between to_date(date_sub(trunc(now(),'month'), interval 2 months)) and to_date(date_sub(trunc(now(),'month'), 1))