“How to filter items with date in the same month/year as current date in Power Automate, e.g. Created date column value is 2021-09-15 or 2021-09-01 and current date is 2021-09-29.”
If you use Power Automate to build reports and reminders, you often work with a date. You don’t want all SharePoint items at the same time, but only the ones that are relevant for the report/reminder. The ones that were recently created, need a review, are waiting for approval, or require some other date related task.
All the previous reminder posts were based on a specific date – flows running daily working with today’s date. But what about a monthly report? A report running once a month, processing all items with date in the current month?
Get the start of a month
You’ll need two dates to build the right Filter Query in the ‘Get items’ action – the start and the end of the month.
Power Automate has an expression that’ll give you the start of a month from any date. Enter a date as a parameter to the startOfMonth(…) expression and it’ll return the first day at 00:00. For example, get the beginning of the current month:
startOfMonth(utcNow())
Since the date of this post is 2021-10-03, the expression will return the start of October 2021.
That’s the first date you need. The next one is the end of the month.
Get the end of a month
Unlike the start of a month, there’s no expression to get the end of a month directly. But you can calculate it if you combine two expressions together. The first one is the startOfMonth(…) to get the start of this month, as explained above. And since the next month is exactly 1 month away, you can add a month to that date.
addToTime(startOfMonth(utcNow()),1,'Month')
The result (at the time of this post) will be the start of November 2021.
That’s the second date you need. Now it’s time to combine them in the Filter Query.
Build the Filter Query
You’re looking for all items with a date between the two dates. The earliest possible date is the start of the month. The latest possible date is just before the start of the next month.
date <is greater than or equal> start of the month
AND
date <is less than> start of the next monthTranslating it to multiple filters in the ‘Get items’ Filter Query:
Date ge 'startOfMonth(utcNow())' and Date lt 'addToTime(startOfMonth(utcNow()),1,'Month')'
Note: Date is my custom date column internal name, yours might be different. Because it’s using the ge and lt operators, you don’t care about the date format even for date only columns.
Such ‘Get items’ will return only items with the relevant date in the current month.
Summary
When you filter items with date in a specific month in Power Automate, you must work with a range. You need the start date, the end date, and filter only items in between. In this situation it’s the start of the month and the start of the next month. The operators then define that dates at the start of the month should be included, but dates at the start of the next month shouldn’t.
The solution above is using a custom Date column and current date, but you can use any date column and any month. Change the column, change the utcNow(…) expression for a specific date, and filter items only in that specific month.
And once you’ve got the items, you could format them in a readable way and send to the users.
There’s also a possibility to get the items with an HTTP request as explained by Paul.
Hi Tom, there is other solution to get the last date of the month:
addDays(addtotime(startOfMonth(utcNow()),1,’Month’),-1).
Thank you for your Power School!
Hello Maciej,
that solution will work for date only columns, but if you have date & time column you’d miss all items created in the last day of the month as it would return last day at 00:00:00. A safer solution would be to remove 1 second for the start of the next month
addSeconds(addtotime(startOfMonth(utcNow()),1,’Month’),-1)to take the last day at 23:59:59.Aha, thank’s for the correction.
Hello Tom, from Brazil. In your example, the functions are for the current year 2022. And how to filter all the birthdays in the month of October? For example, In the Birthday column, there are 1991-10-01, 1985-02-13, and 2001-10-15 and the result will be 1991-10-01 and 2001-10-15.
Hello Leiz,
check this video by Paul where he explains such solution: https://www.youtube.com/watch?v=RqR0wTUbJoY
I have a Sharepoint list of items that I want to filter by date. For example, if today is 4 July, I want all items where the date is between 1 June and 30 June. Your post above is very similar. The only difference seems to me that in the expression, the ‘1 ‘ should be replaced by ‘-1’. Unfortunately, I keep getting the following error message: The expression Created ge 2023-06-01T00:00:00.0000000Z and Created lt 2023-07-01T00:00:00.0000000Z is not valid. Can you help me with this?
Hello Koen,
you’re missing the single quotes around the dates, it should be:
Created ge ‘DATE’ and Created lt ‘DATE’