Recently, I developed a Power Apps dashboard for the Travel Requests application. There, I was required to filter the number of travel requests based on travel type within the selected date range, which needs to be displayed on the Power Apps line chart.
In this article, I will explain how to filter Power Apps line chart based on Date range with a simple example.
Filter Power Apps Line Chart Based On Date Range
I have a SharePoint list named “EmployeeTravelRequests.” It has a TravelType choice column and a Date column. We’ll filter for each travel type and the number of requests within the selected date range.
In the example below, I have a Power Apps line chart displaying the number of travel requests for each travel type.
When I select the dates in the From and To date picker controls, the line chart filters the number of travel requests according to the date range.
To achieve this, follow the steps below!
1. Connect the SharePoint list with the Power Apps application. Then, add a Line chart from the +Insert tab. Also, two DatePicker controls should be added to filter the line chart.
2. Provide the formula below in the OnChange property of the date picker control [DatePicker_From].
Set(varFromDate,DatePicker_From.SelectedDate);
ClearCollect(
colFilteredTravelRequests,
Filter(EmployeeTravelRequests,
Date >= varFromDate && Date <= varToDate
)
)
Here,
- varFromDate = Variable that stores the selected date.
- EmployeeTravelRequests = SharePoint list name.
- Date = SharePoint list date column name.
- colFilteredTravelRequests = Collection that contains the output of the filter function.
- varToDate = Variable name that will be created on other date picker control on change property.
3. Provide the formula below in the OnChange property of date picker control [DatePicker_To].
Set(varToDate,DatePicker_To.SelectedDate);
ClearCollect(
colFilteredTravelRequests,
Filter(EmployeeTravelRequests,
Date >= varFromDate && Date <= varToDate
)
)
varToDate variable contains the selected date.
4. Add the formula below in the Line Chart Items property.
AddColumns(
AddColumns(
GroupBy(
AddColumns(
ShowColumns(
colFilteredTravelRequests,TravelType
),
TravelTypeColumn,
TravelType.Value
),
TravelTypeColumn,
GroupedRequests
),
'Number of Requests',
CountRows(ThisRecord.GroupedRequests)
),
labelvthNumbers,
Concatenate(
TravelTypeColumn,
"(",
'Number of Requests',
")"
)
)
The above code creates two columns, labelvthNumbers and ‘Number of Requests.’ It fetches data from the colFilteredTravelRequests collection that we created above.
- TravelType = SharePoint list choice column name.
- The AddColumns() function adds a column to the collection.
- ShowColumns() functions display the column that we specified.
- The Concatenate () function adds the number of requests next to the travel type name.
- The GroupBy() function creates a table with records grouped based on the travel type column.
- CountRows() function counts the number of records in the grouped table.
5. To display the values while filtering, set the ShowValues property to true.
6. Open the Properties of Line Chart on the right pane -> Select Advanced -> Under Items -> Provide the following.
- Label – Select labelvthNumbers from the dropdown.
- Series – Select the Number of Requests from the dropdown.
Note: Add reset icons for two date picker controls to reset the selected.
Syntax: Reset(DatePicker control name);
Now, save and publish the app. While previewing, select the dates using two date picker controls; the line chart will display the number of requests between the selected date range.
I hope you understand how to display the number of users per SharePoint list choice column based on a selected date range.
You can refer to this article while trying to implement any date filter-related things on the Power Apps line chart.
Also, you may like:
- Power Apps search function
- Create Power Apps Repeating Table With New Form
- Power Apps patch function
- Filter Power Apps gallery control
- Sort Power Apps gallery from the SharePoint list choice column
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com