cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustFlyRC
Frequent Visitor

Filtering on Dates

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.

5 REPLIES 5
RandyHayes
Super User
Super User

@JustFlyRC 

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?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
v-siky-msft
Community Support
Community Support

Hi @JustFlyRC ,

 

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

 

 

@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.

  1. List of 'Holiday' names (so user can pick the Holiday)
  2. List of 'Holiday Dates'
    • Holiday Name (Relationship)
    • Date
  3. 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!

 

 

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

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,046)