Search

PowerApps: Filtering a SharePoint List By Date

Microsoft PowerApps is a powerful Low-Code Platform which allows you to connect to hundreds of data sources – SharePoint is one of them and a very popular choice in enterprises. A very common thing to implement is a list of items respectively records of a data source – in this example a SharePoint list. This works by navigating to Insert → Gallery → Vertical and adding the SharePoint list as data source as shown in the official documentation.

Having inserted the gallery including the connected SharePoint list, let’s try to filter the list by a date. For our example, we will use the following elements in order to create an exmaple:

• Gallery with the SharePoint list items → Gallery
• SharePoint list → SharePointList
• Selected date from date picker in PowerApps used for filtering → DatePicker.SelectedDate
• SharePoint list date (field to filter at SharePoint side) → SharePointListDate

Using the Filter() function, we can create the following formula and insert it on the Gallery.Items property:

Filter(SharePointList, SharePointListDate = DatePicker.SelectedDate)

However, this formula which works in almost every other case in PowerApps doen’t work when filtering a SharePoint List by date! It doesn’t work to directly pass a date into the filter formula. Instead, we have to pass the day, month and year separately into the formula.

It works like this by adding the following formula to the Gallery.Items property:

Filter(SharePointList, SharePointListDate <= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

Please keep in mind that the above displayed formula only works for dates which are equal or larger than SharePointListDate. In order to create the “ultimate filtering formula” for filtering for one exact date, we have to create a second filtering criteria:

Filter(SharePointList, SharePointListDate <= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)), SharePointListDate >= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

As you can see, this isn’t a very professional way of filtering a SharePoint list by date. However, as far as I am aware, there isn’t any other possibility a this point of time and the presented workaround is the only way to go.