Filtering based on date range

[MrtnM]

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:

This is the complete Flow as it looks right now.

[yashag2255 · Accepted solution]

Hey @MrtnM 

I did it like this and it works fine: 

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!

[MrtnM]

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?

[yashag2255]

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]

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?

[yashag2255]

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!