Filter Gallery using DateRange filter

[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 🙂

[eka24]

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.

[Sampath_Kumar]

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 )

[eka24]

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.

[Sampath_Kumar]

Tried below ones 

[eka24]

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.

[Sampath_Kumar]

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 🙂

[v-siky-msft]

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

[Sampath_Kumar]

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?

[v-siky-msft]

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