cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Julien2
Post Prodigy
Post Prodigy

Filter list of items based on current quarter and current year

Hello everyone,

I want to create a flow that should filter an SP list of items based on the current quarter and current year.

The list attributes are only title and date column:

ClassList.PNG
As you see the above screenshot shows that some items are in Q3, one is in Q2, and the other is in Q3 but the year is 2020 (that means it should not be returned in the get items result).

So the flow should return all the items in the current quarter and current year.

I know that there is no direct way to get the current quarter but I think it's feasible to do it with some tricks.

Can please someone assist to build that flow and provide an example?

Any help will be highly appreciated.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User
Super User

Hi @Julien2 

 

Thanks @rohentz  for the solution. I believe the requirement asks for current year only.

 

I have tried the below.

 

image.png

 

image.png

image.png

 

Above I used the below expression for all the quarter dates range

 

formatDateTime(utcNow(),'yyyy')
 
Next find the current quater and date range
 
image.png
 
Above expressions are as follows:
 
add(div(sub(int(formatDateTime(utcNow(), 'MM')),1),3),1)
 
variables('QuarterFromToDates')[sub(outputs('Current_Quarter'),1)]
 
Finally we can do the Get List using SharePoint Connector
 
image.png
 
Above expressions are as follows. Here I have split the date range using the first() and split() expressions.
 
first(split(outputs('Current_Quarter_Date_Range'),','))
 
last(split(outputs('Current_Quarter_Date_Range'),','))
 
Thanks


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

5 REPLIES 5
rohentz
Microsoft
Microsoft

Hi @Julien2 how are you?

 

@Julien2 maybe you can try one of this two solutions. 

 

The first one is to do the query using an HTTP request like the sample below. Filtering the date using the month function, for sample, quarter 1: _vti_bin/listdata.svc/ListName?$select=Title,Schedule&$filter=(month(Schedule) ge 1) and (month(Schedule) le 3) 

 

For others quarters change the filter values

 

rohentz_1-1632146806721.png

 

Or you can create a calculated column on SharePoint with :

 

=INT((MONTH(Schedule)-1)/3)+1

 

And them do a query using :

 

_vti_bin/listdata.svc/ListName?$select=Title,Schedule&$filter=Quarter eq 1

 

rohentz_2-1632146940078.png

 

Please let me know if this information helped to answer your question.

 

Regards

 

Rodrigo Hentz

Microsoft Support Engineer

 

abm
Super User
Super User

Hi @Julien2 

 

Thanks @rohentz  for the solution. I believe the requirement asks for current year only.

 

I have tried the below.

 

image.png

 

image.png

image.png

 

Above I used the below expression for all the quarter dates range

 

formatDateTime(utcNow(),'yyyy')
 
Next find the current quater and date range
 
image.png
 
Above expressions are as follows:
 
add(div(sub(int(formatDateTime(utcNow(), 'MM')),1),3),1)
 
variables('QuarterFromToDates')[sub(outputs('Current_Quarter'),1)]
 
Finally we can do the Get List using SharePoint Connector
 
image.png
 
Above expressions are as follows. Here I have split the date range using the first() and split() expressions.
 
first(split(outputs('Current_Quarter_Date_Range'),','))
 
last(split(outputs('Current_Quarter_Date_Range'),','))
 
Thanks


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

Hello @rohentz ,

All is well thank you!

Thank you for providing an example and those two solutions.

I have tried the second solution, followed the steps mentioned and the HTTP request has extracted the right records.

However, the Date column in the output body of the request is returned in the below number format as it should be in date format:
DateFormat.PNG

The second thing, my requirement was to get in the flow the date range of the current quarter in the current year within power automate.

Because we are hardcoding the number of a quarter inside the query where it will be much better if I can have a variable that returns the current quarter of the month/year and it will filter the quarter date ranges against the date column in the list.

Thanks again!

Hello @abm ,

I would like to thank you so much for spending time to build that amazing solution to filter by the current quarter range and current year dynamically.

That flow is freaking awesome!! Thank you for always supporting and helping this community.

I wish I could give more than 1 Kudos but the folks around here will do so when they see that solution.

Cheers,
Julien

Thank you @abm for providing the needed solution, that was really needed because there is no direct way to solve this problem, we really appreciate it, Keep up this fantastic work.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,058)