Answered

Filter on Calendar from 3 columns within SharePoint List

(0) Share

Report

Posted on by M1stopher_Chris

I've got a calendar in a canvas app which is linked to a SharePoint list. I want the calendar to show if there are items booked on that day. There are 3 columns in the SharePoint list called 'Start Date', 'Second Date' and 'Third Date'. I'm using the below formula which shows any items that has all 3 dates. When it only has a 'Start Date' and 'Second Date', the items do not show and I can't work out how to change the formula so it looks at the 'Second Date' column.

CountRows(
Filter(
'SharePoint List Name',
DateAdd(
_firstDayInView,
ThisItem.Value
) >= 'Start Date' && DateAdd(
_firstDayInView,
ThisItem.Value
) <= 'Third Date'
)
)

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

mmbr1606 14,605 Super User 2025 Season 2 on at

Like (1)

Report
hey @M1stopher_Chris

please try this:
CountRows( Filter( 'SharePoint List Name', DateAdd(_firstDayInView, ThisItem.Value, Days) >= DateTimeValue(Text('Start Date')) && ( (IsBlank('Third Date') && DateAdd(_firstDayInView, ThisItem.Value, Days) <= DateTimeValue(Text('Second Date'))) || (!IsBlank('Third Date') && DateAdd(_firstDayInView, ThisItem.Value, Days) <= DateTimeValue(Text('Third Date'))) ) ) )

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
Verified answer

M1stopher_Chris 31 on at

Like (0)

Report

Hi @mmbr1606 Thanks for the quick response. It works if I remove each of the 'Days' part of the formula, not 100% sure why that makes a difference.

Is there a way of amending the formula so it doesn't use CountRows so I can get away from the delegation warning?

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report

Try thisinstead

ClearCollect(
FilteredItems,
Filter(
'SharePoint List Name',
DateAdd(_firstDayInView, ThisItem.Value) >= DateTimeValue(Text('Start Date')) &&
(
(IsBlank('Third Date') && DateAdd(_firstDayInView, ThisItem.Value) <= DateTimeValue(Text('Second Date'))) ||
(!IsBlank('Third Date') && DateAdd(_firstDayInView, ThisItem.Value) <= DateTimeValue(Text('Third Date')))
)
)
);
// To get the count
CountRows(FilteredItems)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
M1stopher_Chris 31 on at

Like (0)

Report

It's not showing any dates on the calendar and still showing a delegation warning.

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report

Lets try this

Put this in the onvisible of first screen:
Set(startDate, DateTimeValue(Text('Start Date')));
Set(endDate, If(IsBlank('Third Date'), DateTimeValue(Text('Second Date')), DateTimeValue(Text('Third Date'))));

And this as filter:

ClearCollect(
FilteredItems,
Filter(
'SharePoint List Name',
DateAdd(_firstDayInView, ThisItem.Value) >= startDate &&
DateAdd(_firstDayInView, ThisItem.Value) <= endDate
)
);

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
M1stopher_Chris 31 on at

Like (0)

Report

Thanks for trying but it doesn't seem to like it.

OnVisible currently I have the below. I tried adding the additional bit you provided and it provided an error saying that the Start Date wasn't recognised. I also took out my original code and replaced with your suggestion and it didn't like that either.

/*resets calendar view and date selection to today*/
Set(_dateSelected, Today());
Set(_firstDayOfMonth, DateAdd(Today(), 1 - Day(Today()), TimeUnit.Days));
Set(_firstDayInView, DateAdd(_firstDayOfMonth, -(Weekday(_firstDayOfMonth) - 2 + 1), TimeUnit.Days));
Set(_lastDayOfMonth, DateAdd(DateAdd(_firstDayOfMonth, 1, TimeUnit.Months), -1, TimeUnit.Days));
Set(_calendarVisible,true)

Was this reply helpful? Yes No