Showing results for 
Search instead for 
Did you mean: 
New Member

filter array for a calculated date field



I have created a simple flow that is triggered once a day. The objective of this flow is to go and check, on daily basis, the expiry date and if any item meets that condition an email will be sent to the user. 


So on the sharepoint list I have created a calculated column which calculates the expiry date based on the issuance date. 


I know that Flow doesn't accept filtering calculated fields/column in Get Item action. Some suggest to create a Filter Array. I did but unfortunately the filter is not filtering and it is sending an email to everyone in the list on daily basis even though the expiry date is way far from today's date. 


Any one can help please? 




Super User
Super User

I would need to see some sample data for CertificateExpiry to help figure out why your filter is not working. In the mean time uou should add a condition to check if the Filter Array action returns any results. If not, do nothing, else process the results in your Apply to each loop:






Thank you for your response? Do you mean the formula that I used for the date field calculations? So basically, the date is calculated based on the another a number field called "Critical". The expiry date varies subject to the content of the Critical field. 


Here's the formula I used to create the calculated field "CertificateExpiryDate" - 

=IF(Critical=1,DATE(YEAR([Certificate Issuance Date]),MONTH([Certificate Issuance Date])+11,DAY([Certificate Issuance Date])),DATE(YEAR([Certificate Issuance Date]),MONTH([Certificate Issuance Date])+35,DAY([Certificate Issuance Date])))
Looking forward for your reply. 
Super User
Super User

I think what is happening is that the date values you are comparing in the Filter array action are not equal, even though they appear to have the same date in SharePoint:


The CertificateExpiryDate field is a calculated field and the value we see displayed in SharePoint in the above example is 14/09/2021. But internally SharePoint stores this calculated date value along with a time stamp (midnight) as 2021-09-14T00:00:00Z (UTC format):


So when you use the CertificateExpiryDate field in the Filter Array action and compare it with the formatted date from the UtcNow() function you are actually comparing two different values:


I suggest that you try formatting the CertificateExpiryDate as yyyy-MM-dd in the Filter Array action:


Try that and let me know how you get on.


Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

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

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,177)