Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Triggers
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
  • Get help with flow
Menu

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

Posted on October 3, 2021October 10, 2021 by Tom

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

5 thoughts on “Filter SharePoint items with date in the same month in Power Automate”

  1. Maciej says:
    October 4, 2021 at 9:43 am

    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!

    Reply
    1. Tom says:
      October 4, 2021 at 6:11 pm

      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.

      Reply
  2. Maciej says:
    October 5, 2021 at 12:43 pm

    Aha, thank’s for the correction.

    Reply
  3. Leiz says:
    November 2, 2022 at 3:47 pm

    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.

    Reply
    1. Tom says:
      November 13, 2022 at 6:23 pm

      Hello Leiz,
      check this video by Paul where he explains such solution: https://www.youtube.com/watch?v=RqR0wTUbJoY

      Reply

Leave a Reply Cancel reply

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

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


NEW! Master the HTTP requests to SharePoint with a new cheat sheet!

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.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Use Power Automate to forward Outlook events upon registrationJanuary 29, 2023
  • Why the condition is false for the same numbers (Power Automate)January 25, 2023
  • How to forward event invitation to other calendar (Power Automate)January 22, 2023
  • Run ‘For selected item’ flow from non-default environment (Power Automate)January 18, 2023
  • Hide button in SharePoint list after Power Automate flow startedJanuary 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes