Filtering on Dates

[Anonymous]

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.

[RandyHayes]

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

[v-siky-msft]

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

[GarethPrisk]

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

[Anonymous]

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

[Anonymous]

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