Dynamic Date Range Filtering in Power Apps Galleries

Filtering a gallery by date is one of the most frequent tasks in Power Apps. Whether you need to show today’s orders, this week’s tasks, or last quarter’s sales, a reliable date range formula keeps your app responsive and accurate. Instead of hardcoding many separate filter formulas, you can build a single dynamic pattern that adapts to different periods. In this article, you’ll create a reusable solution that handles custom ranges, relative periods (today, this week, this month), and next/last N periods, all while keeping an eye on delegation.

Scenario: Project Deadline Dashboard

Imagine a SharePoint list called Projects with these columns:

• ProjectName (Text)
• Deadline (Date only)
• Status (Choice: Not Started, In Progress, Completed)

You want a gallery that lets analysts quickly choose a predefined period (Today, This Week, This Month, Next Month, Last Month, Custom…) and see only the projects whose deadlines fall within that window. A dropdown selects the period, and for custom ranges two date pickers appear. The gallery’s Items property uses a single flexible formula that relies on a pair of variables.

Centralising the Date Range Logic

Add a dropdown named ddPeriod with items ["Custom", "Today", "This Week", "This Month", "Next Month", "Last Month"]. You can extend it later with quarters, years, and N‑periods.

For the custom option, place two date pickers dpStart and dpEnd and set their Visible property to ddPeriod.Selected.Value = "Custom".

The core work happens in the dropdown’s OnChange (or a button’s OnSelect). Calculate two variables – varStartDate and varEndDate – so the gallery only needs a simple date comparison.

Switch(ddPeriod.Selected.Value,
"Custom", Set(varStartDate, dpStart.SelectedDate); Set(varEndDate, dpEnd.SelectedDate),
"Today", Set(varStartDate, Today()); Set(varEndDate, Today()),
"This Week", Set(varStartDate, Today() - Weekday(Today(), StartOfWeek.Monday) + 1);
             Set(varEndDate, Today() - Weekday(Today(), StartOfWeek.Monday) + 7),
"This Month", Set(varStartDate, Date(Year(Today()), Month(Today()), 1));
              Set(varEndDate, DateAdd(Date(Year(Today()), Month(Today()) + 1, 1), -1)),
"Next Month", Set(varStartDate, Date(Year(Today()), Month(Today()) + 1, 1));
              Set(varEndDate, DateAdd(Date(Year(Today()), Month(Today()) + 2, 1), -1)),
"Last Month", Set(varStartDate, Date(Year(Today()), Month(Today()) - 1, 1));
              Set(varEndDate, DateAdd(Date(Year(Today()), Month(Today()), 1), -1))
)

Gallery Items Property

With the variables ready, the gallery formula becomes clean and fully delegable:

Filter(Projects, Deadline >= varStartDate, Deadline <= varEndDate, Status <> "Completed")

If your date column includes time (DateTime type), change the end date comparison to catch all times on the final day:

Filter(Projects, Deadline >= varStartDate, Deadline < DateAdd(varEndDate, 1))

Adding N‑Periods Forward / Backward

To support “Next 3 weeks” or “Previous 5 days”, add a numeric input nInput, another dropdown ddUnit with ["Days", "Weeks", "Months"], and a button to recalculate. In the button’s OnSelect:

Set(varCount, Value(nInput.Text));
Set(varStartDate, Today());
Set(varEndDate, DateAdd(Today(), varCount, Days))

For weeks, calculate the start of the current week first, then add the number of weeks (minus one day to finish the final week):

Set(varCount, Value(nInput.Text));
Set(varStartDate, Today() - Weekday(Today(), StartOfWeek.Monday) + 1);
Set(varEndDate, DateAdd(varStartDate, varCount * 7 - 1, Days))

For months, use the same logic with the month‑start formula shown in the earlier switch. The same pattern works for quarters and years.

Common Mistakes & Troubleshooting

• Empty date pickers: When using Custom, ensure both dates are selected before filtering. Use a label or the Visible property of a warning control: If(IsBlank(dpStart.SelectedDate) || IsBlank(dpEnd.SelectedDate), "Please select a start and end date").
• Delegation warnings: Perform all date arithmetic on the variable side, never directly on the column. For example, Filter(Projects, DateAdd(Deadline, 5) > Today()) is not delegable.
• UTC vs local time: Today() and date pickers use your local time zone, while SharePoint stores dates in UTC. A date‑only column avoids confusion; for DateTime columns, test with your own time zone offset.
• Blank dates: If any records have an empty Deadline, they will be excluded by the comparison. Add a separate condition if you want to include or flag them.
• Performance: Recalculate varStartDate and varEndDate only when the user changes the period, not in the gallery’s Items every time a control is repainted.

Final Recommendation

By centralising the date range logic into variables, you keep your gallery formulas clean, delegable, and easy to maintain. The same pattern can be reused across multiple screens and data sources. Start with the custom date pickers, then extend with relative periods and N‑period support. Always test with real data and check for delegation warnings.

For even more combinations (next N years, previous N quarters, etc.) refer to the original source that inspired this article.

References

• Matthew Devaney, “Power Apps Filter A Gallery By Date Range Examples”, https://www.matthewdevaney.com/power-apps-filter-a-gallery-by-date-range-examples/
• Microsoft Learn: Filter function – https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup
• Microsoft Learn: DateAdd function – https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-dateadd-timeadd