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?
Hadn't seen that you had amended the post, my apologies. It works a treat
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
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.
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!
Apologies, my bad. I was getting muddled up with SQL.
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.
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
Hi, Overlap is coming back as an 'unknown or unsupported function' when entered.
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
You can use the Overlap function...
Filter('Loan Calendar', Overlap(StartDate, EndDate, 'StartDate.Date'.SelectedDate, 'EndDate.Date'.SelectedDate))