Filter SharePoint items with date in the same month in Power Automate

“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())
Power Automate filter date month

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')
Power Automate filter date 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 month

Translating it to multiple filters in the ‘Get items’ Filter Query:

Date ge 'startOfMonth(utcNow())' and Date lt 'addToTime(startOfMonth(utcNow()),1,'Month')'
Power Automate filter date 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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

3 Comments

Add a Comment

Your email address will not be published. Required fields are marked *