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)

TitleCalendarDate
Monday, January 1, 20181/1/2018
Tuesday, January 2, 20181/2/2018
Wednesday, January 3, 20181/3/2018
Thursday, January 4, 20181/4/2018
Friday, January 5, 20181/5/2018
Tuesday, December 31, 202412/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
    )
)



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
)



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

Filter(
    'Calendar Dates',
    CalendarDate >= StartDate,
    CalendarDate <= EndDate
)




Output

Shows only rows with a date in the next 2 months. Current date is 6/16/2021

TitleCalendarDate
Wednesday, June 16, 20216/16/2021
Thursday, June 17, 20216/17/2021
Friday, June 18, 20216/18/2021
Saturday, June 19, 20216/19/2021
Sunday, June 20, 20216/20/2021
Monday, June 21, 20216/21/2021
Tuesday, June 22, 20216/22/2021
Wednesday, June 23, 20216/23/2021
Thursday, June 24, 20216/24/2021
Friday, June 25, 20216/25/2021
Saturday, June 26, 20216/26/2021
Sunday, June 27, 20216/27/2021
Monday, June 28, 20216/28/2021
Tuesday, June 29, 20216/29/2021
Wednesday, June 30, 20216/30/2021
Thursday, July 1, 20217/1/2021
Friday, July 2, 20217/2/2021
Saturday, July 3, 20217/3/2021
Sunday, July 4, 20217/4/2021
Monday, July 5, 20217/5/2021
Tuesday, July 6, 20217/6/2021
Wednesday, July 7, 20217/7/2021
Thursday, July 8, 20217/8/2021
Friday, July 9, 20217/9/2021
Saturday, July 10, 20217/10/2021
Sunday, July 11, 20217/11/2021
Monday, July 12, 20217/12/2021
Tuesday, July 13, 20217/13/2021
Wednesday, July 14, 20217/14/2021
Thursday, July 15, 20217/15/2021
Friday, July 16, 20217/16/2021
Saturday, July 17, 20217/17/2021
Sunday, July 18, 20217/18/2021
Monday, July 19, 20217/19/2021
Tuesday, July 20, 20217/20/2021
Wednesday, July 21, 20217/21/2021
Thursday, July 22, 20217/22/2021
Friday, July 23, 20217/23/2021
Saturday, July 24, 20217/24/2021
Sunday, July 25, 20217/25/2021
Monday, July 26, 20217/26/2021
Tuesday, July 27, 20217/27/2021
Wednesday, July 28, 20217/28/2021
Thursday, July 29, 20217/29/2021
Friday, July 30, 20217/30/2021
Saturday, July 31, 20217/31/2021
Sunday, August 1, 20218/1/2021
Monday, August 2, 20218/2/2021
Tuesday, August 3, 20218/3/2021
Wednesday, August 4, 20218/4/2021
Thursday, August 5, 20218/5/2021
Friday, August 6, 20218/6/2021
Saturday, August 7, 20218/7/2021
Sunday, August 8, 20218/8/2021
Monday, August 9, 20218/9/2021
Tuesday, August 10, 20218/10/2021
Wednesday, August 11, 20218/11/2021
Thursday, August 12, 20218/12/2021
Friday, August 13, 20218/13/2021
Saturday, August 14, 20218/14/2021
Sunday, August 15, 20218/15/2021