Filter gallery using dates

[Anonymous]

Am having difficulty figuring out the exact syntax to achieve the following:

In my gallery I have a label that shows a date  LblActualExpirydate  It's a calculated label with the details formatted as follows:  

If(IsBlank(LblExpiryDate.Text),LblCalcExpiryDate.Text,LblExpiryDate.Text) 

I want to filter the gallery for items where LblActualExpirydate is today +30 or less 

They are all labels ! so I know I need to format them as date values rather than string but struggling with that also..

Any suggestions?

[Pstork1]

You can't filter off a label in the gallery since the filter is applied before the gallery is created. Your filter has to be applied to a value in the datasource, not one that has been processed for inclusion in the gallery.  What is the formula that calculates LblExpiryDate?

[JayMagness]

Hi,

There are a few ways to do this if you want send me an email and we can do a teams call.

jay@research-nest.com

Jay

[eka24]

Try:

Filter(Datasource,DateDiff(ThisItem.DateColumn,Today(),Days)<=30)

or

Filter(Datasource,DateDiff(Text(ThisItem.DateColumn,ShortDate),Text(Today()ShortDate),Days)<=30)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[KrishnaV]

Hi @Anonymous ,

You can always convert the string value to date with the function DateValue(string) and coming to filter you can get the data as below:

Filter('Employee Details',Text(Created,"[$-en-US]dd-mm-yyyy") = DateAdd(DateValue(Label40.Text),-30,Days))

The above formula gets all the recorded older than 30 days from the date mentioned on the label you are free to change the filter as per the need.

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[Anonymous]

Thanks @Pstork1  yeah that actually makes sense I hadn't thought of that !

The reason it's a calculated label is because i couldn't figure how to do make this field at the datasource (sharepoint list) end LOL

If(IsBlank(LblExpiryDate.Text),LblCalcExpiryDate.Text,LblExpiryDate.Text)

Basically I have 2 expiry date fields in the data source one is a calculated field returning text and one is a date picker.   All entries will all have something in the calculated field and only some in the date picker.  I want a field  (actual expiry date) which says if the dp is empty then use the calculate field date if not use the date picker date. and that's what I've done in the above label formula.  But I now want to filter the gallery based on the actual expiry date.. and use it for various scenarios,  today + 30 + 60 + 90 etc..

Any suggestions on how I can do this, if I can't use a label to filter by?

[Anonymous]

@KrishnaV that works if I can use a field directly from my data source but unfortunately as you can see from my response to pstork1 I'm not doing that.. so if you have any other ideas?

Many thanks

[Pstork1 · Accepted solution]

If you have access to change the calculated field in the list itself.  Could you change the calculation of that calculated field to be either the datepicker or the calculation you use now if the datepicker is empty.  Then you could just filter the list based on the calculated field.  That would avoid the whole problem.

[Anonymous]

Thanks Yes I've been playing around with that I do have access  but I can't seem to get the syntax right to create that calculation in the sharepoint list !How are you on sharepoint calculated fields ??! 🙂

[JayMagness]

If you want goto this teams pointer

https://teams.microsoft.com/dl/launcher/launcher.html?url=%2f_%23%2fl%2fmeetup-join%2f19%3ameeting_M...