Welcome to my Power Apps guide on how to filter a gallery by a date range.
Inside you will find 26 examples of common scenarios filtering a Power Apps gallery by date range. Each page contains code and visuals to show what the code is doing. COPY + PASTE code from the guide into your own app. Just change the datasource name and column names to match your own.
This guide will grow as I add more examples so be sure to bookmark it!
Basics
Filter A Date Range By Day
- Current Day (Today)
- Next Day (Tomorrow)
- Dates Next In ‘N’ Days
- Previous Day (Yesterday)
- Dates In Previous ‘N’ Days
Filter A Date Range By Week
Filter A Date Range By Month
Filter A Date Range Quarter
Filter A Date Range By Year
Did You Enjoy This Article?
Subscribe at the bottom of this page to have new articles sent directly to your inbox the moment I post them.
Questions?
If you have any questions or feedback about Power Apps Filter A Gallery By Date Range Examples please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
This is very helpful indeed
Akbar,
I’m glad to hear it. Thank you for taking the time to leave me this message.
Just what I was looking for this last month!
Ninett,
So close… oh well, least you have it for the next one!
Very timely! I just needed it today
Hi Bejeweled One!
I recognize you from the Power Apps forums. Glad I could help!
Awesome. This definitely helps. Thank you!.
I have a quick question. How do I get the week number for the current week in canvas app?
Jeteen,
You can find my repo of Date functions for Power FX (and Power Apps) on Github here:
https://github.com/pnp/powerfx-samples/tree/main/samples/date-functions
To find the week number code like this is required where StartDate (date), EndDate (date) and DayWeekStart (number) are parameters you must define.
Sum(
AddColumns(
ForAll(
Sequence(
DateDiff(
Date(Year(StartDate),1,1),
StartDate,
Days
)
),
Date(Year(StartDate),1,1) + Value
),
“IsWeekStart”,
Weekday(Value)=DayWeekStart
),
IsWeekStart
)
How would you filter by Name and weekly date range ?
Moyura,
You could add something like “And Name = value” to one of the week filtering techniques I provided.
I have 2 lists.
1 has the list of cars information about the car with registration plate etc.
2 has a startdate and endate and the selected car numberplate when booked.
I want to have users select a startdate and an enddate. then the gallery should shown only the cars that are available for booking during that datetime period. .
Andre,
I think the Items property of the gallery would look something like this:
Filter(
‘SharePoint List’,
CarBookingStartDate >= drpStartDate.SelectedDate,
CarBookingEndDate <= drpEndDate.SelectedDate) )
Jacob,
I think it would be like this:
With(
{
StartDate: Date(
Year(Today()),
Month(Today())-2, // change this number
Day(Today())
)+1,
EndDate: Date(
Year(Today()),
Month(Today()),
Day(Today())
)
},
| Sort(
Filter(
‘Calendar Dates’,
CalendarDate >= StartDate,
CalendarDate <= EndDate ), CalendarDate, Ascending ) )
Here is an interesting challenge i am trying to resolve.
I would like a gallery to produce a meeting agenda, the gallery will pull up the form of that specific item and a decision can be added to it directly in the meeting. I have set up the form and the gallery. However filtering that gallery by the current meeting is proving tricky.
I would like a drop down that says something like current meeting, last meeting and next meeting. This then populates a date range to look between so the gallery only populates items for the current meeting.
I have a SP list with the meeting dates in. I am just struggling with the actual doing of it. Any help would be useful.
Jez,
I suggest you take this question to the Power Apps forums https://powerusers.microsoft.com/t5/Forums/ct-p/PA_Comm_Forums.
Wonderful – thanks so much!
Dan,
You’re welcome 🙂
how do i filter a gallery by todays and upcoming date by clicking on a button
Lyanouluwa,
Put this code in the OnSelect property of the button:
ClearCollect(colFilteredDates, Filter(‘Calendar Dates’, CalendarDate >= Today());
Then use this code in the Items property of the gallery:
colFilteredDates
How would I filter by today’s day and month but ignore the year. I want to use this for a sharepoint list named EmployeeBirthdays where the date column is named Birthday. Each employees birthday is entered in mm/dd/yyyy format. I need the gallery to return anyone in the list with todays month and day.
Luke,
Something like:
Filter(‘Employee Birthdays’, BirthdayMonth=Month(Today), BirthdayDay=Day(Today))
Thank you. I’ll give it a try today and let you know.
any luck for Large list ? When we use filter with Date in Large list Delegation issue is comming.
Hi Matt,
I have the following situation :
a first gallery with Items set to : ForAll(Sequence(4),{itemLine:Value})
a nested gallery with items set to : ForAll(
Sequence(13),
{weekNumber: (ThisItem.itemLine – 1) * 13 + Value}
)
I’d like to show projects with end date occurring in the sequence of the second gallery but I can’t manage to add the filter function to Sequence in Gallery 2.
Any suggestions?
Thank you in advance
Hi Matthew, thank you for this, it is very helpful.
I have still one problem. I have a list with people and in the same list i have a date field (check in). I want to see only those people where the date field is 14, 28, 42, 56, 70, .. days from today’s date.Is this possible?
Below you see the formula i used for just one period of 14 days but i need every 14 days.
SortByColumns(
With(
{
Startdate: Today() – 14,
Enddate: Today() – 13
},
Filter(
‘ListName’,
‘ColumnName’ >= Startdate,
‘ColumnName’ <= Enddate
)
),
“Kamer_”,
SortOrder.Ascending
)
Thanks for helping
Gtz Danny
These were so helpful, Matthew. Thank you.
helpful but does not tell me how to filter for only missing dates, Datefield is null or datefield is blank.
I achieved it by using
Filter(
sharepointList,
If(
!IsBlank(inputFrom.SelectedDate),
sharepointColumnFrom >= inputFrom.SelectedDate,
true
),
If(
!IsBlank(inputTo.SelectedDate),
sharepointColumnTo <= inputTo.SelectedDate,
true
)
)
Hi Matthew!
My gallery is blank if nothing is in the date picker. How can I show all items if the filter is blank?
The actual filter works when dates are selected, but if no dates then I get a blank gallery.