Blog

Search - How to filter records by date ranges (eg today, this week, this month, etc)

A common requirement is to search records based on date ranges. Example date ranges could include records that have been entered today, this week, this month, this year, etc. This post demonstrates how to build this type of functionality.

A common requirement is to search records based on date ranges. Example date ranges could include terms such as today, 'this week', 'next week', 'this month', 'next month', 'this year', and so on.

To demonstrate, we'll use the SharePoint list that's shown beneath, and we'll walk through how to filter the 'CreateDateTime' field of this list based on date ranges.

Building a table of previous date ranges

The first step is to build a table of date ranges. The formula beneath shows how we can carry out this task, based on formulas that were highlighted in my previous post.

http://powerappsguide.com/blog/post/filter-records-by-specific-date-todays-date-or-range-of-dates

Table(
    {
        Desc: "Today",
        StartDate: Today(),
        EndDate: DateAdd(
            Today(),
            1,
            Days
        )
    },
    {
        Desc: "This week",
        StartDate: DateAdd(
            Today(),
            -(Weekday(
                Today(),
                StartOfWeek.MondayZero
            )),
            Days
        ),
        EndDate: DateAdd(
            Today(),
            -(Weekday(
                Today(),
                StartOfWeek.MondayZero
            )) + 7,
            Days
        )
    },
    {
        Desc: "This month",
        StartDate: Date(
            Year(Today()),
            Month(Today()),
            1
        ),
        EndDate: DateAdd(
            Date(
                Year(Today()),
                Month(Today()),
                1
            ),
            1,
            Months
        )
    },
    {
        Desc: "This quarter",
        StartDate: Date(
            Year(Today()),
            (RoundUp(
                Month(Today()) / 3,
                0
            ) * 3) - 2,
            1
        ),
        EndDate: DateAdd(
            Date(
                Year(Today()),
                (RoundUp(
                    Month(Today()) / 3,
                    0
                ) * 3) - 2,
                1
            ),
            3,
            Months
        )
    },
    {
        Desc: "This year",
        StartDate: Date(
            Year(Today()),
            1,
            1
        ),
        EndDate: Date(
            Year(Today()) + 1,
            1,
            1
        )
    }
)
The screenshot beneath illustrates the appearance of this output, as applied to a data table control. The description field describes the date range, the 'start date' field defines the start date of the range, and the 'end date' field defines the 'exclusive' end date. For example, given the start date of today (2nd Aug 2021), the end date of this date range will be 3rd Aug 2021 - that is, anything less than 3rd Aug 2021 will fall into 'today's' date range.

Building a search screen that filters records by date ranges

The next step is to build a screen where users can filter the list based on a date range. To do this, we add a radio control to a screen (called rdoDateRanges in this example) and we set the Items property to the above formula that defines a table of date ranges.


In this example, notice how it's possible to set the default radio choice by setting the default property to the text description of the date range (eg "Today").

We can now add a gallery control and filter our SharePoint list based on the selected radio choice. The formula we use looks like this ([@Issue] is the name of the SharePoint list):

Filter([@Issue], 
       CreateDateTime >= rdoDateRanges.Selected.StartDate 
       And 
       CreateDateTime < rdoDateRanges.Selected.EndDate
)

We can no run our app and use the radio control to filter the records based on the selected date range.


Building a table of upcoming/future date ranges

The logical extension of this example is to provide additional date ranges. To include additional future date ranges (ie, next week, next month, next quarter, next year), we can the formula beneath:

 
Table(
    {
        Desc: "Today",
        StartDate: Today(),
        EndDate: DateAdd(
            Today(),
            1,
            Days
        )
    },
    {
        Desc: "Next week",
        StartDate: DateAdd(
            DateAdd(Today(), 7, Days),
            -(Weekday(
                DateAdd(Today(), 7, Days),
                StartOfWeek.MondayZero
            )),
            Days
        ),
        EndDate: DateAdd(
            DateAdd(Today(), 14, Days),
            -(Weekday(
                DateAdd(Today(), 14, Days),
                StartOfWeek.MondayZero
            )),
            Days
        )
    },
    {
        Desc: "Next month",
        StartDate: Date(
            Year(DateAdd(Today(),1, Months)),
            Month(DateAdd(Today(),1, Months)),
            1
        ),
        EndDate: DateAdd(
            Date(
                Year(DateAdd(Today(),1, Months)),
                Month(DateAdd(Today(),1, Months)),
                1
            ),
            1,
            Months
        )
    },
    {
        Desc: "Next quarter",
        StartDate: Date(
            Year(DateAdd(Today(),3, Months)),
            (RoundUp(
                Month(DateAdd(Today(),3, Months)) / 3,
                0
            ) * 3) - 2,
            1
        ),
        EndDate: 
            DateAdd(
                Date(
                    Year(DateAdd(Today(),3, Months)),
                    (RoundUp(
                        Month(DateAdd(Today(),3, Months)) / 3,
                        0
                    ) * 3) - 2,
                    1
                ),
                3,
                Months
            )
    },
    {
        Desc: "Next year",
        StartDate: Date(
            Year(DateAdd(Today(),1, Years)),
            1,
            1
        ),
        EndDate: Date(
            Year(DateAdd(Today(),1, Years)) + 1,
            1,
            1
        )
    }
)

This screenshot illustrates the output based on a current date of 2nd Aug 2021.

Conclusion

A common requirement is to return records based on date ranges. This post demonstrated how to build this feature based on a SharePoint data source.
Previous
Next
Related posts