Unanswered

Filtering on Dates

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I'm trying to develop an app that filters the gallery based on dates. I have a drop down for users to pick a holiday. For whatever holiday they pick, I'm attempting to filter the gallery based on dates on a SharePoint list. For instance if the user chooses Christmas, I need to filter the gallery to show entries of December 25 for the last 3 years. I'm having issues filtering on more than one date.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Can you give a little more context on your scenario? You stated "filtering on more than one date". Does this mean that users can choose multiple holidays (i.e. Christmas, New Year, etc.) or that you are concerned over the filtering for the last 3 years based on 1 holiday?

Was this reply helpful? Yes No
v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

For instance:

Filter('SP list', 'Date Column'=DateAdd(dropdown.Selected.Date,1,Years) || 'Date Column'=DateAdd(dropdown.Selected.Date,2,Years) || 'Date Column'=DateAdd(dropdown.Selected.Date,3,Years))

PS: Dropdown.Items resembles this:

Table({Holiday:"Christmas",Date:DateValue("12/12/2020")})

If this doesn't make sense, please elaborate on your scenario.

Sik

Was this reply helpful? Yes No
GarethPrisk 2,828 on at

Like
a
(0)

Report
Copy link

Link copied!
@v-siky-msft , that DateAdd would be forward-looking, FWIW. It would get Holidays for the years after the selected date. I think the OP is looking for previous/all instances of the Holiday existing in the list.

It may not be delegable, but that might not be an issue. Simplest thing would be to break it into the Month and Day components, since Holidays typically have a fixed Month/Day

https://en.wikipedia.org/wiki/Public_holidays_in_the_United_States shows some of the common fixed in the US, for example.

Please see attached sample app for how you could accomplish this by using Month and Day parts.

For rotating Holidays, you'll need an alternate approach.

List of 'Holiday' names (so user can pick the Holiday)

List of 'Holiday Dates'

Holiday Name (Relationship)

Date

Then your filtering moves from doing a Day/Month comparison, to an IN operator

User picks Holiday = Thanksgiving

The Holiday Date table has multiple rows for Thanksgiving (for example)

11/28/2019

11/26/2020

You then filter your other list where Date in Filter('Holiday Date', 'Holiday Date'[@Holiday] = Holiday).Date - something to that effect

Good luck!

PowerUsers Fixed ...

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

It is filter over the last 3 years based on the holiday.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

This looks good. I'll give it a try.

Was this reply helpful? Yes No