Efficient Date Filtering in Power Automate

Date-based filtering is pivotal in many applications, especially when sifting through large datasets. This guide elucidates methods to seamlessly filter records in Power Automate by specific dates, today’s date, or even a range of dates. To better explain, we’ll be using a SharePoint list of issues as our primary example.

1. Filtering by Today’s Date

When dealing with data sources that encompass a time component (for instance, columns with a ‘Date and Time’ datatype), straightforward equality checks aren’t feasible. Instead, we require the less than/greater than operators. For displaying records created today on a gallery control, the items property can be set as:

    Filter(Tickets, 
           EntryDateTime >= Today(),  
           EntryDateTime < DateAdd(Today(), 1, Days)
    )
    

2. Filtering by a Specific Date

Should there be a need to filter gallery records by a user-specified date, a date picker control, labeled as dateSearch, can be incorporated. The gallery control’s items property would then be:

    Filter(Tickets, 
           EntryDateTime >= dateSearch.SelectedDate,  
           EntryDateTime < DateAdd(dateSearch.SelectedDate, 1, Days)
    )
    

3. Displaying All Records Absent User-Input

If no date is input by the user, we can set up the gallery to showcase all records. Ensure the date picker control’s isEditable property is true, which grants users the liberty to omit the date:

    Filter(Tickets, 
          (
           EntryDateTime >= dateSearch.SelectedDate And  
           EntryDateTime < DateAdd(dateSearch.SelectedDate, 1, Days)
          ) 
          Or IsBlank(dateSearch.SelectedDate)
    )
    

4. Filtering by a Date Range

For user-defined start and end dates, integrate two date picker controls named dateStart and dateEnd. Here’s how to modify the items property of the gallery control:

    Filter(Tickets,
           EntryDateTime >= dateStart.SelectedDate,  
           EntryDateTime < DateAdd(dateEnd.SelectedDate, -1, Days)
    )
    

If either date isn’t specified by the user, all dates subsequent to the user-defined start or antecedent to the end date are displayed. Here’s the adaptable formula:

    Filter(Tickets, 
      (EntryDateTime >= dateStart.SelectedDate 
       Or IsBlank(dateStart.SelectedDate)
      )
      And
     (EntryDateTime < DateAdd(dateEnd.SelectedDate, 1, Days) 
      Or IsBlank(dateEnd.SelectedDate)
     )
    )
    

5. Radio Option for Filtering Records

Another way to improve UX is by providing a radio option. This allows users to view all records or just today’s records. Start by introducing a radio control, with the items property set to:

    ["Today","All dates"]
    

Then, contingent on the selected radio option, adapt the gallery control’s items property:

    If(radioDateFilter.Selected.Value="Today",
       Filter(Tickets, 
           EntryDateTime >= Today(),  
           EntryDateTime < DateAdd(Today(), -1, Days)
       ),
       Tickets
    )
    

Conclusion

Date-based filtering is integral for building responsive and user-friendly interfaces, especially in Power Automate. This guide presented various scenarios like displaying records of a given date, range, or those created today. For advanced customization and deeper insights on filter query power automate, considering exploring official documentation at Microsoft and PowerApps.com. If you’re encountering challenges or need specialized guidance, don’t hesitate to contact us for tailored assistance.