Canvas Apps - Filtering on Date Fields

Filtering on Date Fields

– I’ve always found working with Dataverse date columns in Power Apps temperamental. I’ve always found a solution that works, but the nagging doubt has remained that I wasn’t completely clear why there was a problem and hence how I’d solved it

This week it happened again when I was comparing the date in a DatePicker control to values in a Dataverse ‘Date only’ column and displaying the filtered results in a gallery. ‘Date only’ columns are where the date stored doesn’t include a time. Depending on how I structured the formula, sometimes the expected data was displayed and sometimes not! Equally, sometimes an error is visible on screen and sometimes not

When I get unexpected results from a Power FX formula, particularly when I’ve been working on an app for a while, my first instinct is to save and reload the app to get a fresh version, because this clears the memory cache and sometimes this addresses the problem. This time that didn’t work and I was determined to get to the root of the problem and find out what I was doing wrong. It took quite a bit of trial and error but I got to the bottom of it!

Composing Filter Expressions

I’ve aways thought of the Filter function as being very forgiving, and it doesn’t matter which way around the data is entered. For example, when using a gallery to display a Dataverse table, adding either of the following formulas to the Items property of the gallery produces the same result:

Filter(myTable,Name=TextInput1.Text)
Filter(myTable,TextInput1.Text=Name)

Similarly, the 4 formulas below produce the same result where TextInput2 is a number

Filter(myTable,TextInput2.Text>Number)
Filter(myTable,TextInput2.Text<Number)
Filter(myTable,Number>TextInput2.Text)
Filter(myTable,Number<TextInput2.Text)

Also, when doing a direct comparison between two ‘Date only’ fields, the forumlas below yield the same result

Filter(myTable,Date=DatePicker1.SelectedDate)
Filter(myTable,DatePicker1.SelectedDate=Date)

So I aways assumed that it would work the same way for all date comparisons, but I WAS WRONG!

The two formulas below, where the Dataverse column is listed first, work fine

Filter(myTable,Date>DatePicker1.SelectedDate)
Filter(myTable,Date<DatePicker1.SelectedDate)

But the two below, with the DatePicker1 control listed first do not. They result in an empty gallery with no data and no screen error

Filter(myTable,DatePicker1.SelectedDate>Date)
Filter(myTable,DatePicker1.SelectedDate<Date)

I double checked this by wrapping the formulas in a CountRows function, and displaying the result in a Label and now the following error is generated

And when the app is run, the same content is displayed in a banner

So the guidance is simple, when filtering a Dataverse date column from a control, the Dataverse column must be listed first

In future I will be following this principle at all times when using the Filter function

Canvas Apps – Filtering on Date Fields

Filtering on Date Fields

Composing Filter Expressions

More Posts on the Filter function

Leave a Comment Cancel Reply