If you’ve ever needed to filter data with dates, you may have run into a few delegation issues depending on the data source you’re using.
For example, filtering dates by a specific date, month, or year is not delegable in Sharepoint and SQL Server. See the Microsoft delegation doc for more details: Understand Delegation in Power Apps
Let’s say you have a column for “Created Date” and you want to be able to filter by data, month, year, etc. You can create three columns for month, day, and year to be able to filter without any delegation issues.
Step 1 - Create three number columns for:
•Created Month
•Created Day
•Created Year
Step 2 – Form Submission / New Record
Set the default property values in your form for the date columns to the follow:
Created Month = Value(Month(Today()))
Created Day = Value(Day(Today()))
Created Year = Value(Year(Today()))
If this form is used for both new and editing records, then you can modify the default property to:
If(Form1.Mode = FormMode.New, Value(Month(Today())),Parent.Default)
Step 3 - Filter Data
Now you can filter your data in various ways without worrying about delegation issues.
Example 1 - Filtering Gallery by Date Picker Selection
Set your Gallery's Item property to:
Filter(YourDataSource,And(Value(CreatedMonth) = Value(Month(DatePicker1.SelectedDate)), Value(CreatedDay) = Value(Day(DatePicker1.SelectedDate)), Value(CreatedYear) = Value(Year(DatePicker1.SelectedDate))))
Example 2 - Filtering Gallery by Months
We can filter our gallery by date filters like Last Month, This Month, Next Month. To do so, I've added a collection and gallery for the filter.
Step 1 - Add a collection to your apps on start property with:
ClearCollect(colDateFilters,{Label:"All Dates",MonthYear: ""},{Label:"Last Month",MonthYear: Month(DateAdd(Today(),-1,Months)) & Year(DateAdd(Today(),-1,Months))},{Label:"This Month",MonthYear: Month(Today()) & Year(Today())},{Label:"Next Month",MonthYear: Month(DateAdd(Today(),1,Months)) & Year(DateAdd(Today(),1,Months))})
Step 2 - Add a gallery for the filter
Set the gallery's item property to your collection.
Step 3 - Update the item property of your gallery with all your data to:
Filter(YourDataSource,If(MonthFilterGallery.Selected.Label = "All Dates",CreatedMonth <> Blank(),CreatedMonth & CreatedYear = MonthFilterGallery.Selected.MonthYear))