Skip to main content

Notifications

Power Apps - Building 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?

  • SS-23070435-0 Profile Picture
    SS-23070435-0 28 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

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

  • WarrenBelz Profile Picture
    WarrenBelz 145,691 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    @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
    SS-23070435-0 28 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    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.

  • timl Profile Picture
    timl 34,525 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    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!

  • Kosenurm Profile Picture
    Kosenurm 802 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

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

  • SS-23070435-0 Profile Picture
    SS-23070435-0 28 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    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. 

     

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,151 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    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
    SS-23070435-0 28 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

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

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,691 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    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

     

     

  • Kosenurm Profile Picture
    Kosenurm 802 on at
    Re: Filtering in Gallery using dates where days are before and after selected date

    You can use the Overlap function...

     

    Filter('Loan Calendar', Overlap(StartDate, EndDate, 'StartDate.Date'.SelectedDate, 'EndDate.Date'.SelectedDate))
    

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard