How to filter the data by Dates

[MrAdam]

Hello, 

I was plan to create Attendance Apps using PowerApps. So I have problem, how to filter the data by dates. For example, I want to know who not attend in class last Friday or last month. When I choose one dates and the data in that date only will display in my apps.

All the data I can filter by search their name. But I have problem when I want to search the data by dates. Im sorry because Im newbie in PowerApps. Please Help me.

Any suggestions?

Thank You so much

[yashag2255]

Hi @MrAdam 

You can create two date pickers to capture the start and end date for the data retrieval.

Expression that can be used to filter out the results:

Filter(DataSourceName, DateColumn >= StartDateDatePicker.SelectedDate && DateColumn <= EndDateDatePicker.SelectedDate)

If you could share more details about the schema of the datasource along with the exact requirements, we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[MrAdam]

Thank you for your reply.

This is my project. I was use only one date. I want to filter the data in the SearchBox. If you can help me. 

Thank You

[v-xida-msft]

Hi @MrAdam ,

Could you please share a bit more about your scenario?

Do you want to type date value within the Searchbox to filter data within your Gallery?

Based on the needs that you mentioned, I think the Filter function and AddColumns function could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       AddColumns('DataSource', "TextDateValue", Text(DateColumn)),
       TextSearchBox1.Text in NameColumn ||
       StartsWith(TextDateValue, TextSearchBox1.Text)
)

Note: The DateColumn and NameColumn are both columns from your DataSource. Currently, the date type column could not be used as Search column within the Search function, you could only specify Text type column as Search column in Search function.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Eelman]

@v-xida-msft 

Would this AddColumns work on large datasets?

The reason I ask is because I've added extra columns to my SP Lists which have the dates converted to numbers in the format: yyyymmdd. This way I can filter using numbers instead of dates but you way seems a lot easier - especially if it works on large data sets.

[v-xida-msft · Accepted solution]

Hi @Eelman ,

Currently, within PowerApps, the AddColumns function is not a Delegable function, so it would process data locally in your app rather than delegate the process to data source itself.

Within PowerApps app, you could only process 2000 records locally at most. Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

If the data source records of your SP list is more than 2000, you could consider bulk-load your data source records into collection in your app, then use the collection as data source in your app. Please check and see if the following article would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sort-gallery-with-multiple-fields/td-p/37973...

https://powerusers.microsoft.com/t5/Building-Power-Apps/Delegation-Formula-Help/td-p/393454

Best regards,

[Eelman]

@v-xida-msft 

Ah ok, got it. Thanks mate!