“I’m using Power Automate to filter items that were created exactly 6 months ago, but the Filter Query doesn’t return anything!”
The Filter Query in the ‘Get items’ action is one of the most powerful features in Power Automate. Instead of processing all the items, you can get only the relevant ones. You’ll save API calls, the flow will be cleaner and easier to read, all because of a single filter. But to use Filter Query correctly it’s necessary to understand the data you’re working with. Especially when filtering by a date column.
In most situations you’ll do just fine with the date only column. You format it using the standard ISO format ‘yyyy-MM-dd’ and compare the dates. But what if you can’t work with a date without time? What if you must use a date and time column? More specifically, the Created column to get the items created a specific number of days ago?
Use a date range
As explained in the ancient article on event reminders, you must work with a range. The item could be created at any time during the day. It could be at 00:00:01 in the morning, or 23:59:59 in the evening, and you must include all the possibilities. That’s why you need a combined filter. A filter that’ll take everything that was created between the start of the day and the end of the day.
To continue with the example on filtering items created exactly 6 months ago, you’ll need that date at 00:00:00. Start from today’s date and remove 6 months. Adding the date format ‘yyyy-MM-dd’ will take care of the time part. If there’s no time, it’ll automatically take 00:00:00 as the time.
addToTime(utcNow(),-6,'Month','yyyy-MM-dd')That’s the bottom range for the filter, beginning of the desired day.
The second step is to get the end date, the end of the day. Or, to make it a bit easier, the beginning of the next day.
addDays(addToTime(utcNow(),-6,'Month','yyyy-MM-dd'),1)That’s the upper range, the date and time you don’t want to include.
All that’s left is combining the expressions in a single Filter Query:
Created ge 'addToTime(utcNow(), -6, 'Month', 'yyyy-MM-dd')' and Created lt 'addDays(addToTime(utcNow(), -6, 'Month','yyyy-MM-dd'),1)'
Note: make sure to use the right operators! The bottom range is the day including 00:00:00. The upper range is the next day excluding the 00:00:00 (= ending with 23:59:59…).
Summary
When you filter items by their created date in Power Automate, you must always remember that creation date includes time. That’s why you can’t use a simple ‘equals’ filter, you must always use a range. Calculate the start date, the end date, and filter all the items in between.
Hi, I need the same filter but by year. We need to move items from a list to another by year. It’s more like archiving but needed to archive them by year. The older record we have is 2015. Can I change the expression ‘Month’ to ‘Year’ instead? (I’m currently testing it so it may or may not work). If you have a better solution, please help. Thank you so much!
Hello Lisianne,
yes, you can change the ‘Month’ to ‘Year’ and it’ll calculate with years instead of months.
Great article! I was wondering how I can adapt this query to filter created to show last week?
Hello Bill,
it can, just use different values in the AddToTime expression: https://tomriha.com/how-to-add-months-to-a-date-in-power-automate/, e.g. -1 and Week
Hi Tom.
is it possible to use filter query to compare 2 date columns?
Review date > Approval Date.
When I try this, it comes back with a format error.
Thanks
Hello Marina,
it’s not possible, you can reference existing column only on the left side of the condition. If you want to compare multiple columns between each other you’ll have to get all the items in the ‘Get items’ action and then filter them using the ‘Filter array’ action.