Answered

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

(0) Share

Report

Posted on by SS-23070435-0

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'

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

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:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Kosenurm 802 on at

Like (0)

Report
Copy link

Link copied!
You can use the Overlap function...

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

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,496 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
SS-23070435-0 28 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
iAm_ManCat 18,256 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
SS-23070435-0 28 on at

Like (0)

Report
Copy link

Link copied!

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

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.

Was this reply helpful? Yes No
Kosenurm 802 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
timl 36,733 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
SS-23070435-0 28 on at

Like (0)

Report
Copy link

Link copied!
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

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.

Was this reply helpful? Yes No
WarrenBelz 154,496 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
SS-23070435-0 28 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No