Create PowerApps Date filter

In this PowerApps Tutorials, we will discuss how to create a Date filter in PowerApps. Also, we will cover these below things that are related to PowerApps Date Filter as:

  • PowerApps filter SharePoint list by date
  • PowerApps filter gallery by date range
  • PowerApps date range picker or PowerApps Date Validation
  • PowerApps between dates or PowerApps between two dates
  • How to sort the Date column in PowerApps or PowerApps sortbycolumns filter date

Create PowerApps Date filter

As you may know, In PowerApps, you can filter any field values or column values using the PowerApps Filter function. Similarly, also you can filter the Powerapps Date Time column. Let’s take an example.

Suppose I have a SharePoint List named as “Project Details“. This list has some columns with different data types. Also, it has a Date Time column named “Project Created Date” as shown below.

Here what I want to do is, I will create a Powerapps Gallery that will show the last 15 or 30 days of records from the SharePoint list. In this case, We need to create a Date filter in PowerApps.

PowerApps create a Date filter
Create PowerApps Date filter

Follow these below steps that we needs to do:

  • First of all, In the PowerApps app, Connect the SharePoint List Date Source.
  • Then, On the PowerApps Blank screen, Insert a Gallery control (Insert -> Gallery -> Vertical).
  • Select the Gallery control and go to Properties pane (from the right side of the page). Select the Layout as “Title, subtitle, and body”.
  • Click on Edit from the Fields section. Enter these below field values (you can add any field that you want to display in the gallery but the Date field should be there) as:
  1. Body: Select Employee First Name
  2. Subtitle: Select Project Created Date
  3. Title: Select Title
create Date filter in PowerApps

Powerapps create a Date Filter for last 15 Days:

Select the Vertical Gallery control and apply this below formula on its Items property as:

Items = Filter(
    'Project Details',
    'Project Created Date' >= DateAdd(
        Today(),
        -15,
        Days
    )
)

Where,

  • ‘Project Details’ = SharePoint List name
  • ‘Project Created Date’ = Date Time column that is present in the SharePoint list
  • DateAdd = Powerapps DateAdd function helps to add a number of units to a date/time value
  • Today() = It is the Powerapps function that retrieves the current Date

As my Today’s Date is 9/9/2020, So it will filter and display all the last 15 days’ records in the Gallery control.

PowerApps create Date filter

Powerapps create a Date Filter for last 30 Days:

Similarly, if you want to display the last 30 days records in the Gallery control, then apply the below formula on Gallery controls Items property as:

Items = Filter(
    'Project Details',
    'Project Created Date' >= DateAdd(
        Today(),
        -30,
        Days
    )
)

In the below screenshot, you can see all the last 30 days’ records in the Powerapps Gallery control.

create a Date filter in PowerApps
Create PowerApps Date filter

NOTE:

Not only you can retrieve 15 or 30 days records from the SharePoint List, But also, you can retrieve any number of last days. Instead of 15/30, Just you need to apply the number of last days that you want. Then your data will filter and display in the Gallery control.

PowerApps filter SharePoint list by date

By taking a simple scenario, We will see how we can use the Powerapps filter SharePoint list by date.

I have a SharePoint list named “Gadget Details“. This list has some columns with different data types. Also, it contains a Date Time column (Order Date) with a single line of the text data type as shown below.

PowerApps filter SharePoint list by date
  • In the Powerapps screen, connect the SharePoint list Data source to the app.
  • Create one Edit form (Insert -> Forms -> Edit). Enter the SharePoint list name (Gadget Details) in DataSource property.
  • Add a Button and set its Text property to Add.
  • Select the Add Button and set its OnSelect property to SubmitForm(Form1) where Form1 represents the Edit form name.
Power Apps filter SharePoint list by date
Create PowerApps Date filter
  • Create a new variable on Powerapps App’s OnStart property as:
OnStart = Set(TodaysDate, Today())

Where,

  • TodaysDate = Variable name
  • Today() = This function retrieves the current or today’s date
filter SharePoint list by date in Power Apps
Create PowerApps Date filter
  • Select the Date Text field (Order Date) and apply this below formula on its Default Property as:
Default = Text(TodaysDate, "[$-en-US]yyyymmdd")

Once you applied the formula, it will retrieve Today’s date with “yyyymmdd” format.

filter SharePoint list by date in PowerApps
PowerApps create date filter
  • Insert a Vertical Gallery control (Insert -> Gallery -> Vertical). Select the Gallery and go to Properties pane. Make the Layout to Title and subtitle.
  • Click on Edit from Fields section and insert these below values as:
  1. Subtitle: Select Order Date column
  2. Title: Select Title column
filter SharePoint list by date using PowerApps
  • Next, Select the Gallery control and apply this below formula on its Items property as:
Items = Filter('Gadget Details', 'Order Date' = Text(TodaysDate, "[$-en-US]yyyymmdd"))
PowerApps filter SharePoint by date
  • If you want to remove the specific item from the gallery control, then, take a Trash icon (Insert -> Icon -> Trash) and put this below formula on its OnSelect property as:
OnSelect = Remove('Gadget Details',ThisItem)
filter SharePoint list by date using Power Apps
  • Save and Preview (F5) the app. Enter the field values and click on the Add button. By default, the Order Date will be Today’s date. So no need to change the date from the Order Date.
  • Then you can see, the item will be displayed in the gallery. Also, this new item has been added to the SharePoint List.
  • If you want to remove any specific item from the gallery, then click on the Trash icon of that particular item.
PowerApps filter SharePoint list by date field
  • Go to the SharePoint list (Gadget Details). You can see the new record has been added as shown below.
PowerApps filter SharePoint list by datetime field

PowerApps filter gallery by date range

Suppose, there are two Date picker controls named “Select Start Date” and “Select End Date“. What I want to do is,

  1. The user will select a Start Date from Select Start Date.
  2. The user will select an End Date from Select End Date.
  3. PowerApps Gallery Control will display all SharePoint items or records that fall between those two selected dates.

To make this above scenario, follow these below steps:

  • I have a SharePoint list named “SharePoint Project Expenses“. This list has some columns including a Date time column (Project Created Date). Also, it has some records as shown below.
PowerApps filter gallery by date range
  • In PowerApps, Connect the SharePoint list Data source (SharePoint Project Expenses) to apps.
  • On the PowerApps screen, Take two Date Picker controls and rename it to “StartDatePicker” and “EndDatePicker“.
Power Apps filter gallery by date range
  • Insert one Vertical Gallery control. Go to Properties pane and change the Layout to “Title, subtitle, and body“.
  • Click on Edit from the Fields section and enter these below fields as:
  1. Body: Select the Number of User column
  2. Subtitle: Select Project Created Date column
  3. Title: Select Title column
filter gallery by date range in Power Apps
  • Next, Select the vertical Gallery control and apply this below formula on its Items property as:
Items = Sort(
    If(
        IsBlank(EndDatePicker.SelectedDate),
        If(
            IsBlank(StartDatePicker),
            'SharePoint Project Expenses',
            Filter(
                'SharePoint Project Expenses',
                'Project Created Date' >= StartDatePicker.SelectedDate
            )
        ),
        If(
            IsBlank(StartDatePicker),
            Filter(
                'SharePoint Project Expenses',
                'Project Created Date' <= EndDatePicker.SelectedDate
            ),
            Filter(
                'SharePoint Project Expenses',
                'Project Created Date' <= EndDatePicker.SelectedDate,
                'Project Created Date' >= StartDatePicker.SelectedDate
            )
        )
    ),
    'Project Created Date',
    Descending
)

Where,

  • ‘SharePoint Project Expenses’ = SharePoint List name
  • StartDatePicker = Start Date Picker control name
  • EndDatePicker = End Date Picker control name
  • ‘Project Created Date’ = Date Time column name (from SharePoint list)

You can refer the below screenshot.

PowerApps filter by date range
  • Now, Save and Preview (F5) the app. Select Start Date and Select End Date. It will filter the records (that fall between those two selected dates) and display them in the Gallery control as shown below.
PowerApps filter gallery date range

PowerApps date range picker

In this below example, we will see how we can use the Powerapps Date range picker between two dates. That means, we need to validate the Powerapps Date picker values. Let us take a simple scenario.

In the below screenshot, there are two Powerapps Date picker controls named Select Start Date and Select End Date.

As per the scenario, When I will select the End date is less than the Start date, then an error will appear as shown below. Here, the end date should be equal to or greater than the Start date.

PowerApps date range picker

For the above Date validation scenario, follow these below steps that we need to do in Powerapps:

  • Insert two Labels (Insert -> Label). Set one Labels Text property to “Select Start Date” and another Labels Text property to “Select End Date“.
  • Insert two Date Picker controls (Insert -> Input -> Date picker). By default, the dates should be current or Today’s date.
  • Add one Label (To display the Invalid Date error message) and place it above the date picker controls. Select the Label and set its Text property to “Please Enter a valid End Date!! End Date should be greater than the Start Date“. (you can set the text to any error message)

The Powerapps screen looks like below.

Power Apps date range picker
  • Select the Error Label and set its Visible property as:
Visible = DatePickerEnd.SelectedDate < DatePickerStart.SelectedDate

Where,

  • DatePickerEnd = End Date picker control name
  • DatePickerStart = Start Date picker control name

The above code specifies, if the End Date picker value will greater than the Start Date picker value, then the invalid date error will display.

Date range picker in Powerapps
  • Next, select the Start Date picker control and apply this below formula on its BorderColor property as:
BorderColor = If(
    DatePickerEnd.SelectedDate < DatePickerStart.SelectedDate,
    Color.Red,
    RGBA(
        101,
        128,
        187,
        1
    )
)
Date range picker in Power apps
  • Select the End Date picker control and apply this below formula on its BorderColor property as:
BorderColor = If(
    DatePickerEnd.SelectedDate < DatePickerStart.SelectedDate,
    Color.Red,
    RGBA(
        101,
        128,
        187,
        1
    )
)
powerapps limit date picker
  • Just save and preview (F5) the app. Select the Start Date (you can choose any date). Select End Date (the date should be equal or greater than the Start Date). Then you will not see any invalid date error message.
  • But if you will choose the End date is less than the Start date, then you will get an invalid date error message with the red background color as like the below screenshot.
powerapps date picker limit

PowerApps between dates

First of all, we need to understand what does this PowerApps between dates means? Below I have taken a simple example to get these things easily.

I have a SharePoint List named “Project Details“. This list has two Date time columns as “Project Created Date” and “Project End Date“. Both Date columns are having with different dates (Upcoming Dates and as well as Old Dates).

Here, What I want to do is, If the current date and time is in between the Project Created Date and Project End Date, then that specific record will filter from the data source and display in the PowerApps Collections.

In the below screenshot, you can see, In the SharePoint list, there is only one record that is having with the current Date i.e. “9/9/2020“. So I will create a Powerapps Collection and it will filter the record details with the current Date.

PowerApps between dates

Follow these below steps to do so:

  • In the PowerApps screen, Insert a Button (Insert -> Button) and set its Text property to Collect Current Dates.
  • Select the Button (Collect Current Dates) and apply this below formula on its OnSelect property as:
OnSelect = ClearCollect(
    DateCollection,
    Filter(
        'Project Details',
        Now() >= 'Project Created Date',
        Now() <= 'Project End Date'
    )
)

Where,

  • ClearCollect = This is the function that helps to create the Powerapps Collections where always it clears the previous value and stores the new one.
  • DateCollection = PowerApps Collection Name where the value will be stored.
  • ‘Project Details’ = SharePoint List Name
  • ‘Project Created Date’ = It is a Date Time column that is present in the SharePoint List
  • ‘Project End Date’ = It is also a Date Time column that is present in the SharePoint List
PowerApps between two dates
  • Now, Save and Preview (F5) the app. Click on the Button (Collect Current Dates).
  • Then, go to PowerApps Collections (View -> Collections -> Date Collection). You can see the Data has been filtered with the current Date and Time as like the below screenshot.
Power Apps between two dates

PowerApps sortbycolumns filter date

Suppose, In Powerapps, you want to arrange the order of the Dates as Ascending or Descending. Then, in that case, you can use the Sort function to filter the Powerapps Date column.

This below screenshot represents a PowerApps Data Table that contains some fields from a SharePoint List Data source named “Product Details“. This data table also contains a Date column named as “Project Created Date“.

Here, As you can see in the below table, the Date column (Project Created Date) is present with a random order. So, I want to sort the Date column as Ascending or Descending order.

PowerApps sortbycolumns filter date

PowerApps Sort Date column to Ascending order:

To sort the Powerapps Date column as in Ascending order, follow this below things as:

Select the Data Table and apply this below formula on its Items property as:

Items = Sort('Project Details', 'Project Created Date', Ascending)

Where,

  • Sort = This is the Powerapps function name that helps to sort the Date field to Ascending order.
  • ‘Project Details’ = SharePoint List Name that contains all the columns and fields
  • ‘Project Created Date’ = Date Time column that is present in the SharePoint List
  • Ascending = To sort the Date field as Ascending, We need to specify the order type.

Once you applied the above formula, then you can see the Date column has been sorted with an ascending order as shown below.

PowerApps sort by columns filter date

PowerApps Sort Date column to Descending order:

Similarly, if you want to sort the PowerApps Date column as Descending order, then select the Data table and apply the below formula on its Items property as:

Items = Sort('Project Details', 'Project Created Date', Descending)

Where,

  • Descending = To sort the Date field as Descending, We need to specify the order type.
PowerApps sort by date

Also, you may like these below PowerApps Tutorials as:

In this PowerApps Tutorials, We discussed how to create a Date filter in PowerApps. Also, we saw these things as:

  • PowerApps filter SharePoint list by date
  • PowerApps filter gallery by date range
  • PowerApps date range picker or PowerApps Date Validation
  • PowerApps between dates or PowerApps between two dates
  • How to sort the Date column in PowerApps or PowerApps sortbycolumns filter date
>