Filter Gallery using DateRange filter

(0) Share

Report

Posted on by Sampath_Kumar

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 🙂

Categories:

Building Power Apps

Scenario_2.png

data.png

Scenario_1.png

I have the same question (0)

All responses (10)

Answers (0)

eka24 20,923 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sampath_Kumar 16 on at

Like (0)

Report

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 )

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sampath_Kumar 16 on at

Like (0)

Report

Tried below ones

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sampath_Kumar 16 on at

Like (0)

Report

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

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

Thanking you in advance 🙂

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
Sampath_Kumar 16 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report
Hi @Sampath_Kumar ,

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

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

Was this reply helpful? Yes No
Sampath_Kumar 16 on at

Like (0)

Report

Sorry for late reply. As I am out of station unable to reply to the msgs.
I tried the method which you mentioned still i am unable to do.
I added my data in excel and attaching here. can anyone help me please.

Thank you all 🙂

TestData.zip

Was this reply helpful? Yes No