Filter Is Pulling Information Outside of Date Picker Range

[levoncosta]

Hello,

I have a filter where it should be providing results only within a date range provided by Date Pickers. It works for the most part, except it is pulling information outside the range. Apparently, the From date appears to be working, and it is respected. The To date appears to not be working properly, and it is being disregarded. 

Here is the code for the Items property of the gallery: 

SortByColumns(
  Filter(
    [@'ERP Time Entry'], 
    StartsWith(
      'Project Name', 
      SelfSearchBox.Text
    ), 
    Lower('Employee Email') = Lower(User().Email),  // code adjusted here, Lower() used to ensure no mismatching occurs
    'Date (Work)' > RegularDatePickerFrom.SelectedDate,
    'Date (Work)' < DateAdd(RegularDatePickerTo.SelectedDate,30,Days)
  ),
  "Title"
)

Here is the error:

This is clearly not supposed to occur. Any feedback is greatly appreciated. 

Thanks! 

[timl]

Hi @levoncosta 

You're adding 30 days to the 'to' date. Is this intentional, as that would explain the discrepancy?

SortByColumns(
  Filter(
    [@'ERP Time Entry'], 
    StartsWith(
      'Project Name', 
      SelfSearchBox.Text
    ), 
    Lower('Employee Email') = Lower(User().Email),  // code adjusted here, Lower() used to ensure no mismatching occurs
    'Date (Work)' > RegularDatePickerFrom.SelectedDate,
    'Date (Work)' < RegularDatePickerTo.SelectedDate
  ),
  "Title"
)

[levoncosta]

Hi @timl 

This was intentional, but it's honestly inefficient coding from my understanding. Not every month has 30 days. When I removed that part, the code no longer functions as intended. What are your suggestions? 

[timl · Accepted solution]

Hi @levoncosta 

To clarify what you're trying to achieve in your screenshot, your intention is to return all records with a 'Date (work)' value that falls between 5th Jan 2023 to 6th Jan 2023 inclusively?

If that's the case, I'd try the following. If 'Date (work)' has a time component, this will return records up to 23:59 for the end date.

SortByColumns(
  Filter(
    [@'ERP Time Entry'], 
    StartsWith(
      'Project Name', 
      SelfSearchBox.Text
    ), 
    Lower('Employee Email') = Lower(User().Email),  // code adjusted here, Lower() used to ensure no mismatching occurs
    'Date (Work)' >= RegularDatePickerFrom.SelectedDate,
    'Date (Work)' < DateAdd(RegularDatePickerTo.SelectedDate,1,Days)
  ),
  "Title"
)

I notice also how there's an exclaimation mark against your formula. This may be due to the use of the 'lower' function, and is another possible reason why the formula doesn't work as expected if the above doesn't work.

[MJ84]

The code provided by @timld should work.

I don't quite understand why the month length matters if you are specifying the to and from date? 

[levoncosta]

@timl Actually, I tested this, and it appears to work as expected. Great job!