Filter tasks on Planner for all tasks created in the previous month

[roxy1]

Hi,

I'm currently building a flow and the part I am now stuck on is filtering tasks from a Planner that have a completed date for the previous month.

I am comparing planner tasks and SharePoint items from the past month. 

I have a  filter query on the SharePoint list but I am having trouble with the filter array on tasks which comes back with the error :

The expression in the filter array I am using is:

@And(greater(item()?['createdDateTime'], startOfMonth(getPastTime(1, 'MONTH')),less(item()?['createdDateTime'], startOfMonth(utcNow()))

I've so much wonderful help from this community - thank you to everyone who sees this 🙂

[grantjenkins · Accepted solution]

it looks like you're missing a couple of closing ) in your expression. One for greater and one for less.

I would have actually done this slight differently to you (always lots of ways to solve the same thing). I would get the past date (1 month ago) and format it as yyyy-MM as we are only interested in the month within a particular year.

Then I would format each of the task dates with yyyy-MM and see if they're equal to the date one month ago.

See below:

List tasks just gets all the tasks from a particular planner.

Get past time One Month ago gets the current date minus one month

Compose Last Month formats the Past time with yyyy-MM

formatDateTime(body('Get_past_time_One_Month_ago'), 'yyyy-MM')

Filter array formats each item (createdDateTime) with yyyy-MM and checks to see if they are equal to Compose Last Month.

formatDateTime(item()?['createdDateTime'], 'yyyy-MM')

[roxy1]

Wonderful! Thank you for your help again @grantjenkins - happy to learn new ways to solve these issues too. I have not used "get past time" before so thank you!

[roxy1]

Can I pick your brain @grantjenkins - would there be a reason when a filter query on a SharePoint list is not returning items from the 1st of the month?! I am checking the outputs and it is missing two items dated 1/9/22.

The list column setting has a date only value no time

the expressions used are Date ge '@{startOfMonth(getPastTime(1,'MONTH'))}' and Date lt '@{startOfMonth(utcNow())}'

[grantjenkins · Accepted solution]

It's likely due to the UTC time zone and your actual time zone (might be out enough so dates close to the start/end of the month may get missed). I would probably convert the date to your time zone to ensure you are getting the correct results all the time.

[roxy1]

Thanks @grantjenkins that is most very likely the reason. What a pain!