I recently developed a Power Apps Employee Onboarding application in which all of the filtered quarter details must be displayed in the gallery after it has been filtered out quarterly.
In this tutorial, I will show you how to filter Power Apps gallery by quarter, along with the topics below:
- Filter Power Apps gallery by the current quarter
- Filter Power Apps gallery by next quarter
- Working with Power Apps filter gallery by the next N quarters
- Filter Power Apps gallery by last quarter
- Power Apps filter the gallery by previous N quarters
Filter Power Apps Gallery By Current Quarter
Here, I will show you how to filter a Power Apps gallery by the current quarter.
- I have a SharePoint List, [Employee Onboarding], which contains the fields below.
| Column Name | Data Type |
| Employee ID | This is a Title column with a single line of text. I just renamed it to “Employee ID” |
| Employee Name | A single line of text |
| Single line of text | |
| Joinining Date | Date and time |
- There is a Gallery control in Power Apps. This gallery displays each record from the SharePoint list based on the current quarter.
To achieve the above example, follow the below-mentioned steps. Such as:
- Insert a Gallery control [gal_CurrentQuartersRecords] and set its Items property as:
Items = With(
{
StartDate: Date(
Year(Today()),
Month(Today())
-If(
Mod(Month(Today())-1,3) = 0,
0,
Mod( Month(Today())-1, 3)
),
1
),
EndDate: Date(
Year(Today()),
Month(Today()) + 3-Mod(Month(Today())-1,3),
1
)-1
},
Filter(
'Employee Onboarding',
'Joining Date' >= StartDate,
'Joining Date' <= EndDate
)
)Where,
- With() = This function can help us to evaluate a formula for a single record
- StartDate, EndDate = Power Apps Scope Variables
- Year(Today()) = This function returns the year component of a Date/Time value
- Month(Today()) = This function returns the month component of a Date/Time value
- Mod(Month(Today())-1,3) = This function helps us to find the date value from the respective month component
- ‘Employee Onboarding’ = SharePoint List
- ‘Joining Date’ = SharePoint Date Column
- Save, Publish, and Preview the app. The gallery will filter and display all the current quarter records, as shown below.
This is how to filter a Power Apps gallery by the current quarter.
Filter Power Apps Gallery By Next Quarter
Next, we will see how to filter a Power Apps gallery by next quarter.
- In Power Apps, there is a Vertical gallery control. This gallery displays each record from the SharePoint list based on the next or upcoming quarter.
To work around this example, follow the below steps.
- Select the Gallery control and set its Items property as:
Items = With(
{
StartDate: Date(
Year(Today()),
Month(Today())
-If(
Mod(Month(Today())-1,3) = 0,
0,
Mod( Month(Today())-1, 3)
)+3,
1
),
EndDate: Date(
Year(Today()),
Month(Today()) + 3-Mod(Month(Today())-1,3)+3,
1
)-1
},
Filter(
'Employee Onboarding',
'Joining Date' >= StartDate,
'Joining Date' <= EndDate
)
)Where,
- Mod( Month(Today())-1, 3))+3 = This function helps us to find the date value from the respective month component, and ‘+3’ is for the next quarter
Refer to the image below:
- Save, Publish, and Preview the app. The gallery will display the filtered records, i.e., [Next Quarter Records], as shown below.
This is how we can filter a Power Apps gallery by the next/upcoming quarter.
Power Apps Filter Gallery By Next N Quarters
Let’s see how to filter the Power Apps gallery by the next N quarters.
In Power Apps, there is a Vertical Gallery control. This gallery filters and displays each record from the SharePoint list based on the next/upcoming three quarters.
To do so, follow the below-mentioned steps. Such as:
- On the Power Apps Screen -> Insert a Gallery control and set its Items property to the code below.
Items = With(
{
FirstDate: Date(
Year(Today()),
Month(Today()),
Day(Today())
),
LastDate: Date(
Year(Today()),
Month(Today()) + (3 * 3), // You can also change the number of Quarters
Day(Today())
) - 1
},
Filter(
'Employee Onboarding',
'Joining Date' >= FirstDate,
'Joining Date' <= LastDate
)
)Where,
- Month(Today()) + (3 * 3) = This function helps us to find the date value from the respective month component. And 3 is the current quarter, and ‘3’ means number of quarters
- Day(Today()) = Power Apps Day() function returns the day component of a Date/Time value
Refer to the below screenshot:
- Save, Publish, and Preview the app. The gallery will display the filtered records, i.e., [Next 3 Quarters Records], as shown below.
This is all about the Power Apps filter gallery by the next/upcoming ‘N’ quarters.
Power Apps Filter Gallery By Previous Quarter
Similarly, here we will see how to filter a Power Apps gallery by the previous quarter.
- In Power Apps, there is a Horizontal gallery control. This gallery displays each record from the SharePoint list based on the last or previous quarter.
To work around the above example, follow the below steps.
- On the Power Apps Screen -> Insert a Gallery control and set its Items property as:
Items = With(
{
StartDate: Date(
Year(Today()),
Month(Today())
-If(
Mod(Month(Today())-1,3) = 0,
0,
Mod( Month(Today())-1, 3)
)-3,
1
),
EndDate: Date(
Year(Today()),
Month(Today()) + 3-Mod(Month(Today())-1,3)-3,
1
)-1
},
Filter(
'Employee Onboarding',
'Joining Date' >= StartDate,
'Joining Date' <= EndDate
)
)Where,
- Month(Today()) + 3-Mod(Month(Today())-1,3)-3 = This function helps us to find the date value from the respective month component, and ‘-3’ is the number of previous quarters.
Refer to the below screenshot:
- Save, Publish, and Preview the app. The gallery will display the filtered records, i.e., [Last Quarter Records], as in the screenshot below.
This is how to filter a Power Apps gallery by the previous quarter.
Power Apps Filter Gallery By Previous N Quarters
Finally, I will show you how to filter a Power Apps gallery by previous N quarters.
- In Power Apps, there is a Gallery control. This gallery displays each record from the SharePoint list based on the last/previous two quarters.
To achieve it, follow the below steps. Such as:
- On the Power Apps Screen -> Insert a Gallery control and set its Items property code like below.
Items = With(
{
StartDate: Date(
Year(Today()),
Month(Today())-(3*2), // You can also change the number of Quarters
Day(Today())
)+1,
EndDate: Date(
Year(Today()),
Month(Today()),
Day(Today())
)
},
Filter(
'Employee Onboarding',
'Joining Date' >= StartDate,
'Joining Date' <= EndDate
)
)Where,
- Month(Today())-(3*2) = This function helps us to find the date value from the respective month component. 3 is the current quarter, and 2 means the number of quarters
- Save, Publish, and Preview the app. The gallery will display the filtered records, i.e., [Last Two Quarters Records], as shown below.
This is how to filter a Power Apps gallery by previous/last ‘N’ quarters.
Conclusion
I hope this article helped you to understand how to filter the Power Apps gallery by current quarter, previous quarter, next N quarters, and previous N quarters with examples.
Also, you may like some more Power Apps tutorials:
- Power Apps Filter Gallery By Year
- Get Dropdown Selected Value in Power Apps
- Power Apps Filter Gallery By Days
- Power Apps Filter Gallery By Week
- Power Apps Filter Gallery By Date
- How to Filter Power Apps Gallery by Person
Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.
