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

(0) Share

Report

Posted on by roxy1

195

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 🙂

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report
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')

Was this reply helpful? Yes No
roxy1 195 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
roxy1 195 on at

Like (0)

Report

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())}'

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No
roxy1 195 on at

Like (0)

Report

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

Was this reply helpful? Yes No