You are currently viewing Generate dates between two dates in PowerApps
Generate dates between two dates in PowerApps

Generate dates between two dates in PowerApps

Generate dates between two dates in PowerApps, Creating a dynamic table with all dates between two specified dates in PowerApps is a practical skill that can enhance various applications, In this blog post we will walk you through the process of generating a sequential list of dates between two chosen dates, using PowerApps.

Create a table with all dates between two dates in PowerApps

Whether you’re new to PowerApps or an experienced user looking to improve your app, this tutorial will help you easily add this feature. By the end, you can create a table that lists all dates between two selected dates.

In PowerApps Canvas app we will create a gallery with generated dates between two dates “Start Date and End Date” as shown in the image below.

Generate dates between two dates in PowerApps

Generate dates between two dates in PowerApps

Create a Canvas App: Start by creating a new canvas app in PowerApps.

Add Input Fields: Add two DatePicker controls to allow users to select the start and end dates.

  • Name the first DatePicker control StartDatePicker.
  • Name the second DatePicker control EndDatePicker.
  • Add Gallery Control
  • In the gallery item property set the following formula to Create a table with all dates between two dates in PowerApps
ForAll(
    Sequence(EndDatePicker.SelectedDate - StartDatePicker.SelectedDate + 1),
    StartDatePicker.SelectedDate + Value - 1
)

Formula Explanation:

  • ForAll Function:
    • ForAll is a PowerApps function that performs actions on each item in a collection or table. It iterates through each record in a table and executes a specified action for each one.
  • Sequence Function:
    • Sequence creates a table of sequential numbers. The number of elements in the sequence is based on the value you provide.
    • EndDatePicker.SelectedDate – StartDateDatePicker.SelectedDate + 1 calculates the total number of days between the start and end dates, inclusive.
  • EndDatePicker.SelectedDate is the date picked in the end date control.
  • StartDateDatePicker.SelectedDate is the date picked in the start date control.
  • Subtracting these dates gives the difference in days.
  • Adding 1 to include the end date in the range.
  • Then Loop through the Sequence function to generate a sequence of numbers from 1 to the calculated number of days. And the ForAll iterates over this sequence.
  • So the formula within ForAll:

StartDateDatePicker.SelectedDate + Value – 1 computes each date within the range:
StartDateDatePicker.SelectedDate is the starting date.
Value is the current item in the sequence (ranging from 1 to the total number of days).
Subtracting 1 adjusts the sequence to start from 0 (since dates are zero-indexed in this context).


So the final formula :

Sequence(EndDatePicker.SelectedDate - StartDateDatePicker.SelectedDate + 1) 

Generates a sequence of numbers representing each day from the start date to the end date.
ForAll iterates over this sequence, calculating each date by adding the sequence number (minus one) to the start date.
This process effectively Creates a table with all dates between two dates in PowerApps.

image 1 | Power Platform Geeks

Conclusion

By using the ForAll and Sequence functions in PowerApps, you can efficiently generate a list of all dates between two selected dates. This approach leverages the powerful capabilities of PowerApps to manipulate and display date ranges dynamically. Whether you are managing projects, scheduling events, or organizing data, this method provides a robust solution for handling date-driven data. With this knowledge, you can enhance your PowerApps applications, making them more versatile and user-friendly.

See Also
Join us

Heba Kamal

Microsoft MVP, MCT, Technical Speaker, Blogger, and Microsoft 365 and Power Platform Consultant.

Leave a Reply