Cannot filter data table by a longer time period than 7 days

[Anonymous]

Hi all,

I have built a time recording app with a data table on one of the screens. There are various filters on the screen which allows the user to filter the data in the table, including two date pickers which serve as a 'Start Date' and 'End Date' so users can filter by a date range of when a task was started.

For some reason I cannot get the data table to show more than 7 days worth of data, even if the date pickers are set to a larger time span than 7 days. Please see the screenshot below along with the items field of my data table:

Filter(
AddColumns(AllEntries, "NewTaskTime", Text(Value(TaskDuration), "[$-en-US]#,0")),
(
(IsBlank(NamePicker_1.Selected.UserPrincipalName) || AgentID = NamePicker_1.Selected.UserPrincipalName)

&&
(IsBlank(TeamPicker_1.Selected.Result) || Team = TeamPicker_1.Selected.Result)

&&

(CategoryPicker_1.Selected.Result = "All" || Category = CategoryPicker_1.Selected.Result)

&&

StartTime >= DatePicker2.SelectedDate
&&
StartTime < DateAdd(DatePicker2_1.SelectedDate,1, Days)

&&

Value(TaskDuration) >= 0
))

Here is the top of the table: 

Here is the bottom of the table:

You can see that despite filtering until the 11th October, the table stops presenting data on the 8th. Which is 7 days after the start filtering date of the 1st October.

Any help would be much appreciated.

Thanks,

Freddie

[WarrenBelz]

Hi @Anonymous ,

How many items are in your List and what is your Delegation limit set at? Also, try this - although I am a bit confused with you date logic as the second bit is really the same as the first.

With(
   {
      wDate:
      DateAdd(
         DatePicker2_1.SelectedDate,
         1,
         Days
      )
   },
   Filter(
      AddColumns(
         Sort(
            AllEntries,
            ID,
            Descending
         ),
         "NewTaskTime", 
         Text(
            Value(TaskDuration), 
            "[$-en-US]#,0"
         )
      ),
      (
         IsBlank(NamePicker_1.Selected.UserPrincipalName) || 
         AgentID = NamePicker_1.Selected.UserPrincipalName
      ) &&
      (
         IsBlank(TeamPicker_1.Selected.Result) || 
         Team = TeamPicker_1.Selected.Result
      ) &&
      (
         CategoryPicker_1.Selected.Result = "All" || 
         Category = CategoryPicker_1.Selected.Result
      ) &&
      StartTime >= DatePicker2.SelectedDate &&
      StartTime < wDate
   ) &&
   Value(TaskDuration) >= 0
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @Anonymous ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Hi Warren,

Sorry for the delayed response. I was working on another project. I have tried implementing your solution, and whilst it produces the same functionality as the one I was using previously, the filtering still stops after 8 days. Despite the End Date being further ahead than 8 days. I have attached a screenshot. There are definitely entries for this user between the 12th and 19th October, but for some reason they are not included in the data table even though the date filter should capture these entries, and the filter clearly works because it is filtering something.

Thanks,

Freddie

[WarrenBelz]

Hi @Anonymous ,

As you can understand this makes on logical sense unless you have a Delegation limitation which is why I asked how many records you have and suggested you filter with the ID descending to get the most recent record set up to your Delegation limit (which I suggest you set to 2,000 n this case). Can you please think through this logic as clearly something is being overlooked.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Hi Warren,

I have already set the delegation limit to 2000. But there are far fewer than 2000 rows when filtering for this user for a 2+ week long time period. The count figure in the top right hand corner of my screenshot is the number of rows being returned.

Thanks,

Freddie

[WarrenBelz]

Hi @Anonymous 

It has to be one of your other filters - as a test try it with just the date filter and see what you get.

With(
   {
      wDate:
      DateAdd(
         DatePicker2_1.SelectedDate,
         1,
         Days
      )
   },
   Filter(
      AllEntries,
      StartTime >= DatePicker2.SelectedDate &&
      StartTime < wDate
   ) 
)

Here you are filtering for records where the start time is greater than or equal to the second date picker AND less than the day after the first date picker.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.