web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Filtering in Gallery u...
Power Apps
Answered

Filtering in Gallery using dates where days are before and after selected date

(0) ShareShare
ReportReport
Posted on by 28

Part of one the apps I am building involves a booking calendar. The user selects a start date and a end date (using date pickers), this then filters a SharePoint list to show which items are available to book between the selected dates. It uses the following formula - 

 

 

Filter('Loan Calendar', (StartDate >= 'StartDate.Date'.SelectedDate && StartDate <= 'EndDate.Date'.SelectedDate) || (EndDate >= 'StartDate.Date'.SelectedDate && EndDate <= 'EndDate.Date'.SelectedDate))

 

 

This works for finding most existing bookings within the calendar. However, it does find the bookings where the start date is before the selected start date, and the end date is after the selected end date. For example - 

 

Below is the 'Loan Calendar' 

SMStevenson_0-1676365150025.png

If someone then wants to make a booking between 17th Feb and 28th Feb the following is returned

SMStevenson_1-1676365418552.png

 

Item - 'Owl-AV Loan 1' is not shown within the filtered results even though the item is booked out.

 

The formula only returns existing bookings where either the Start Date or the End Date fall within the chosen dates, which means that the formula is working correctly. But is there a way of filtering whereby it sees all items that are booked out between two dates regardless of whether Start Date and End Date are within the selected dates?

Categories:
I have the same question (0)
  • Kosenurm Profile Picture
    802 on at

    You can use the Overlap function...

     

    Filter('Loan Calendar', Overlap(StartDate, EndDate, 'StartDate.Date'.SelectedDate, 'EndDate.Date'.SelectedDate))
    
  • Verified answer
    WarrenBelz Profile Picture
    154,496 Most Valuable Professional on at

    Hi @SMStevenson ,

    You just need an extra possibility

    Filter(
     'Loan Calendar', 
     (
     StartDate <= 'StartDate.Date'.SelectedDate && 
     EndDate >= 'StartDate.Date'.SelectedDate
     ) || 
     (
     StartDate <= 'EndDate.Date'.SelectedDate && 
     EndDate >= 'EndDate.Date'.SelectedDate
     ) ||
     (
     StartDate > 'StartDate.Date'.SelectedDate && 
     EndDate < 'EndDate.Date'.SelectedDate
     )
    )

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

     

     

  • SS-23070435-0 Profile Picture
    28 on at

    Hi, Overlap is coming back as an 'unknown or unsupported function' when entered.

  • iAm_ManCat Profile Picture
    18,256 Most Valuable Professional on at

    There is no such thing as an Overlap function.

    Where are you getting this info from?

     

    Anyone of our veterans ever heard of an Overlap function? 

    @WarrenBelz @timl @ChrisPiasecki @Drrickryp @BCBuizer @RandyHayes @Pstork1 

  • SS-23070435-0 Profile Picture
    28 on at

    Hi, when I enter the additional possibility it gives the following

     

    SMStevenson_0-1676367092030.png

    Which still doesn't include 'Owl-AV Loan 1' and now has 'Projector 2 (Epson)' which is a booking that starts after the selected dates. Not sure why it hasn't picked up 'Owl-AV Loan 1' and then also given a item that isn't within the selected dates. 

     

  • Kosenurm Profile Picture
    802 on at

    Apologies, my bad. I was getting muddled up with SQL. 

  • timl Profile Picture
    36,733 Super User 2026 Season 1 on at

    Yep, there's no Overlap function in Power Apps/PowerFX. However, it would be really neat if it did include this function exactly for this type of usecase!

  • SS-23070435-0 Profile Picture
    28 on at

    I think I have got it work, with the help of the suggestion of an extra possibility. With thinking through the logic of the formula. As 

    (StartDate <= 'StartDate.Date'.SelectedDate && StartDate >= 'EndDate.Date'.SelectedDate)

    Looks for any entry with a Start Date between the two selected dates and 

    (EndDate >= 'StartDate.Date'.SelectedDate && EndDate <= 'EndDate.Date'.SelectedDate)

    Looks for any End Date between the two selected dates. My thinking is to look for any entry where both the Start Date and End Date are outside of the the selected dates, so using

    (StartDate <= 'StartDate.Date'.SelectedDate && EndDate >= 'EndDate.Date'.SelectedDate)

    As each part is an 'Or' function it should return all entries where bookings exist. Putting this all together, the formula gave 

    SMStevenson_0-1676368670543.png

     

    So the whole formula used is

    Filter(
     'Loan Calendar', 
     (StartDate <= 'StartDate.Date'.SelectedDate && StartDate >= 'EndDate.Date'.SelectedDate) || 
     (EndDate >= 'StartDate.Date'.SelectedDate && EndDate <= 'EndDate.Date'.SelectedDate) ||
     (StartDate <= 'StartDate.Date'.SelectedDate && EndDate >= 'EndDate.Date'.SelectedDate)
    )

     

    @WarrenBelz many thanks for the inspiration on this.

     

    I will let you know if it doesn't work or creates any issues.

  • WarrenBelz Profile Picture
    154,496 Most Valuable Professional on at

    @SMStevenson ,

    Please look at amended post - all you really have to do is allow for an instance where the start is before and the end is after the period. I also use other logic on the other two and test either date for being in the period

  • SS-23070435-0 Profile Picture
    28 on at

    Hadn't seen that you had amended the post, my apologies. It works a treat

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 529 Most Valuable Professional

#2
Haque Profile Picture

Haque 230

#3
Kalathiya Profile Picture

Kalathiya 217 Super User 2026 Season 1

Last 30 days Overall leaderboard