Power Apps Filter Date Range Delegation Workaround

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-filter-a-gallery-by-date-range-examples/power-apps-filter-date-range-delegation-workaround/. Author: Matthew Devaney.

Power Apps Filter Date Range Delegation Workaround

I will show you how to use variables to avoid delegation issues when filtering a date range. In this example, we will filter a table to show only dates within the next ‘N’ number of months where N is a number specified by the app maker. Input

Calendar Dates is a SharePoint list with consecutive days starting 1/1/2018 and ending 12/31/2024 (2,557 rows) Title CalendarDate Monday, January 1, 2018 1/1/2018 Tuesday, January 2, 2018 1/2/2018 Wednesday, January 3, 2018 1/3/2018 Thursday, January 4, 2018 1/4/2018 Friday, January 5, 2018 1/5/2018 … … Tuesday, December 31, 2024 12/31/2024 Original Code

The WITH function only partially supports delegation . This means the number or results cannot exceed the non-delegation record limit (500 rows by default, 2000 maximum). With( {

StartDate : Date ( Year(Today()), Month(Today()), Day(Today()) ),

EndDate : Date ( Year(Today()), Month(Today())+ 2 , //change this number Day(Today()) ) -1 }, Filter(

'Calendar Dates' , CalendarDate >= StartDate, CalendarDate <= EndDate ) ) Code language: JavaScript ( javascript ) Delegation-Friendly Code

Set the StartDate and EndDate variables prior to using them inside a filter function. Set ( StartDate,

Date ( Year(Today()), Month(Today()), Day(Today()) ) ); Set ( EndDate,

Date ( Year(Today()), Month(Today())+ 2 , //change this number Day(Today()) ) -1 ) Code language: JavaScript ( javascript )

Use this improved code in the Items property of a gallery. Filter(

'Calendar Dates' , CalendarDate >= StartDate, CalendarDate <= EndDate ) Code language: JavaScript ( javascript ) Output

Shows only rows with a date in the next 2 months. Current date is 6/16/2021 Title CalendarDate Wednesday, June 16, 2021 6/16/2021 Thursday, June 17, 2021 6/17/2021 Friday, June 18, 2021 6/18/2021 Saturday, June 19, 2021 6/19/2021 Sunday, June 20, 2021 6/20/2021 Monday, June 21, 2021 6/21/2021 Tuesday, June 22, 2021 6/22/2021 Wednesday, June 23, 2021 6/23/2021 Thursday, June 24, 2021 6/24/2021 Friday, June 25, 2021 6/25/2021 Saturday, June 26, 2021 6/26/2021 Sunday, June 27, 2021 6/27/2021 Monday, June 28, 2021 6/28/2021 Tuesday, June 29, 2021 6/29/2021 Wednesday, June 30, 2021 6/30/2021 Thursday, July 1, 2021 7/1/2021 Friday, July 2, 2021 7/2/2021 Saturday, July 3, 2021 7/3/2021 Sunday, July 4, 2021 7/4/2021 Monday, July 5, 2021 7/5/2021 Tuesday, July 6, 2021 7/6/2021 Wednesday, July 7, 2021 7/7/2021 Thursday, July 8, 2021 7/8/2021 Friday, July 9, 2021 7/9/2021 Saturday, July 10, 2021 7/10/2021 Sunday, July 11, 2021 7/11/2021 Monday, July 12, 2021 7/12/2021 Tuesday, July 13, 2021 7/13/2021 Wednesday, July 14, 2021 7/14/2021 Thursday, July 15, 2021 7/15/2021 Friday, July 16, 2021 7/16/2021 Saturday, July 17, 2021 7/17/2021 Sunday, July 18, 2021 7/18/2021 Monday, July 19, 2021 7/19/2021 Tuesday, July 20, 2021 7/20/2021 Wednesday, July 21, 2021 7/21/2021 Thursday, July 22, 2021 7/22/2021 Friday, July 23, 2021 7/23/2021 Saturday, July 24, 2021 7/24/2021 Sunday, July 25, 2021 7/25/2021 Monday, July 26, 2021 7/26/2021 Tuesday, July 27, 2021 7/27/2021 Wednesday, July 28, 2021 7/28/2021 Thursday, July 29, 2021 7/29/2021 Friday, July 30, 2021 7/30/2021 Saturday, July 31, 2021 7/31/2021 Sunday, August 1, 2021 8/1/2021 Monday, August 2, 2021 8/2/2021 Tuesday, August 3, 2021 8/3/2021 Wednesday, August 4, 2021 8/4/2021 Thursday, August 5, 2021 8/5/2021 Friday, August 6, 2021 8/6/2021 Saturday, August 7, 2021 8/7/2021 Sunday, August 8, 2021 8/8/2021 Monday, August 9, 2021 8/9/2021 Tuesday, August 10, 2021 8/10/2021 Wednesday, August 11, 2021 8/11/2021 Thursday, August 12, 2021 8/12/2021 Friday, August 13, 2021 8/13/2021 Saturday, August 14, 2021 8/14/2021 Sunday, August 15, 2021 8/15/2021