cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wagner_nathan
Frequent Visitor

Display Gallery Results Based on Current Year and Quarter

I have a sharepoint list with entries that have a completion date associated with each one. I have a gallery that is used to display all the entries from this list. This list can become quite long and I want to make it easier to sort through by only displaying the entries that have a completion date that falls within the current year and quarter. For example if 10 of the entries were completed in April of 2020 and 5 were completed in May of 2020 then I only want the gallery to display those 15 items instead of the whole list. Can anyone help me figure out how to do this? 

3 REPLIES 3
eka24
Super User
Super User

What is the nature of your date column?.

Try:

Filter(Datasource, Text(DateColumn,"yyyy")=Year(Now()))

 

Let me get feedback if it doesn't work show screenshot

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

@eka24 I was unable to get that to work. I have a calculated column in share point with this formula to determine the year: =TEXT([Audit Date],"YYYY").

 

I also have a calculated column to determine the quarter: ="Q"&CHOOSE(MONTH([Audit Date]),1,1,1,2,2,2,3,3,3,4,4,4). These show the correct year and quarter in sharepoint.

 

I am just not sure how to use the information in powerapps. This is my formula for items with the gallery:

Items = SortByColumns(Filter([@'Audit Master'],StartsWith(Section,TextSearchBox1.Text)||StartsWith(Operation,TextSearchBox1.Text)||StartsWith('Auditor Name'.DisplayName,TextSearchBox1.Text)),"Title",If(SortDescending1,Descending,Ascending))

Hi @wagner_nathan .

 

Calculated column is not delegable for sharepoint list in PowerApps, so it doesn't make sense for large data set.

You need to create a real Text column or Number column to store the Year and Quarter by workflow, then filter the gallery by them.

Gallery.Items:

SortByColumns(Filter([@'Audit Master'], NumberYearColumn = Year(Now()) &&  NumberQuarterColumn= RoundUp(Month(Now())/3, 0) &&(StartsWith(Section,TextSearchBox1.Text)||StartsWith(Operation,TextSearchBox1.Text)||StartsWith('Auditor Name'.DisplayName,TextSearchBox1.Text))),"Title",If(SortDescending1,Descending,Ascending))

 

Hope this helps

Sik

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,698)