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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (3,503)