cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrtnM
Level: Powered On

Filtering based on date range

I am building a Flow that gets items from a Sharepoint list, filters them based on date (created in the latest 31 days), puts those in an html table and sends an email.

 

In the list, one item is created in 2017, one in 2018, one in Februari 2019 and three are created yesterday for testing.

 

This is the Filter array expressions I use:

@greaterOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(addDays(utcNow(), -31), 'MM/dd/yyyy'))

 

Everything works, except for the filtering: the filter array collects all the items from yesterday, plus the item from 2017. Why on Earth does this happen? I have tried changing the "-31" to for example -1 (this gets the same result), and -700 (this only collects the 2017 item).

 

I have also tried to replace the expressions in Filter array with 

@greaterOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd'))

but this returns nothing, and

@lessOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd'))

but this of course returns everything.

and also tried to delete the Filter array and instead in the Get items make a Filter query:

sd.png

 

 

This is the complete Flow as it looks right now.

Namnlös.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Filtering based on date range

Hey @MrtnM 

 

I did it like this and it works fine: 

ddeew.PNG

Expression: 

 

addDays(utcNow(),-31)

Note that you need to get items where created date is greater than or equal to that date (today -31)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

5 REPLIES 5
Dual Super User
Dual Super User

Re: Filtering based on date range

Hey @MrtnM 

 

I did it like this and it works fine: 

ddeew.PNG

Expression: 

 

addDays(utcNow(),-31)

Note that you need to get items where created date is greater than or equal to that date (today -31)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

MrtnM
Level: Powered On

Re: Filtering based on date range

Well, that worked smoothly 🙂

Perfect, thanks!

 

Follow up question: 

If insted I would like to collect the items created earlier than -31 days ago (up until -31 days), what would that expression be?

Dual Super User
Dual Super User

Re: Filtering based on date range

Hey @MrtnM 

 

You can just replace the 'ge' with 'le' and it should get you items created prior to the -31 days created date. Ensure that you check the top count in the action as the default returns 100 items only. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

MrtnM
Level: Powered On

Re: Filtering based on date range

Another follow up:

 

If I also would liek to filter items from a yes/no column, that is: filter items from the last 31 days, that also have No in column X, is it possible to do this in the Filter query as well?

Dual Super User
Dual Super User

Re: Filtering based on date range

Hey @MrtnM 

 

Can you try this: after the date filter condition, put an and columname eq true (if it is a yes no type column then the value will be boolean and true for yes, false for no. you need to add the true or false from the expression editor)

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,716)