cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter Gallery using DateRange filter

Hi All,

 

I am facing an issue when filtering the gallery (Large dataset from CDS) using a date range.


1. I tried like date range of single column and its working fine
             Filter(DataSets,'Valid From'>=DatePicker1.SelectedDate && 'Valid From'<=DatePicker2.SelectedDate )
2. Same like above I want to filter data based on two columns like below, but it's not working

             Filter(DataSets,'Valid From'>=DatePicker1.SelectedDate && 'Valid To'<=DatePicker2.SelectedDate )
Note: Max Date of Valid To column is 12/31/9999 (I updated end year like Today()+10000)

If anyone has any idea please help me with this.

 

Thanks 🙂

10 REPLIES 10
Highlighted
Super User
Super User

Re: Filter Gallery using DateRange filter

The two formulas are the same. The one working and the one not working. Please which other column do you want to add.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Frequent Visitor

Re: Filter Gallery using DateRange filter

Hi,

Thanks for the reply.
scenario 1 I used both "Valid from" column only, but in second scenario I changed to "Valid To"
1.  Filter(DataSets,'Valid From'>=DatePicker1.SelectedDate && 'Valid From'<=DatePicker2.SelectedDate )
2. Filter(DataSets,'Valid From'>=DatePicker1.SelectedDate && 'Valid To'<=DatePicker2.SelectedDate )

Highlighted
Super User
Super User

Re: Filter Gallery using DateRange filter

Try

Filter(DataSets,Text('Valid From',ShortDate)>=Text(DatePicker1.SelectedDate,ShortDate) && Text('Valid From',ShortDate)<=Text(DatePicker2.SelectedDate,ShortDate))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Frequent Visitor

Re: Filter Gallery using DateRange filter

Tried below ones 

error_1.png
error_2.png

Highlighted
Super User
Super User

Re: Filter Gallery using DateRange filter

Filter(DataSets,Text(Value('Valid From'),ShortDate)>=Text(DatePicker1.SelectedDate,ShortDate) && Text(Value('Valid From'),ShortDate)<=Text(DatePicker2.SelectedDate,ShortDate))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Frequent Visitor

Re: Filter Gallery using DateRange filter

Hi, Thanks for ur patience and reply.
When I am using the query in the "gallery.Items", it's not working


error_5.png


If I use the same query to display in label its working fine, can you please tell me what will be the issue.


error_3.png

 

Thanking you in advance 🙂

Highlighted
Community Support
Community Support

Re: Filter Gallery using DateRange filter

Hi @Sampath_Kumar ,

 

I feel confused with the logic of the second condition, the date of Valid To column is 12/31/9999 which is much greater than selected date, so nothing will return if the condition is 'Valid To'<=DatePicker2.SelectedDate.

Am I misunderstand something?

BTW, The Text does not support greater than and less than comparisons.

 

Sik

Highlighted
Frequent Visitor

Re: Filter Gallery using DateRange filter

Hi,

 

I want to get the data between a selected date range, in that my "Valid To" column max date is 12/31/9999.
So if I give "valid To"=12/31/2050, I should get all records between dates right?
so how can I filter the gallery (large datasets) using date range?

Highlighted
Community Support
Community Support

Re: Filter Gallery using DateRange filter

Hi @Sampath_Kumar ,

 

Your filter code is correct, and works properly in my test.

Snipaste_2020-05-20_11-27-22.png

If you select the 'Valid To' data picker to 12/31/2050, you can only get the records that the valid to is less than 12/31/2050.

Please make sure it does exist records that valid to is less than 12/31/2050.

 

If you just want to return all records when selecting 12/31/2050 (since it is the maximum date we can select), please try this:

If(DatePicker2.SelectedDate=DateValue("12/31/2050"),
Filter(DataSet,'Valid From'>=DatePicker1.SelectedDate && 'Valid To'<=DateValue("12/31/9999")),
Filter(DataSet,'Valid From'>=DatePicker1.SelectedDate && 'Valid To'<=DatePicker2.SelectedDate)
)

Hope this helps.

Sik

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,534)