Using a calculated date column from SharePoint in a formula on Power Apps to show items expiring soon

[Laela]

Hi there, 

I have created a Calculated column on SharePoint List to calculate an Expiry Date for 4 years after a date which the user inputs. In the calculated column, I've used the following formula:

=DATE(YEAR([User Input Date])+4,MONTH([User Input Date]),DAY([User Input Date]))

Then, on Power Apps, I am attempting to show items that are expiring in the next 6 months by filtering the Expiry Date in a Gallery with the following formula:

Filter('Date source', 'Expiry Date' <= DateAdd( Today(), 180 ),!IsBlank('Expiry (Date'))

I am getting an error in the formula that says "Invalid argument type. Expecting a number value". 

The date is returning as a string and is returning in this format as an example:

yyyy-mm-ddT08:00:00Z

Please help me understand how to get the date in the correct format so that I can use it in a Power Apps formula (i.e. the Gallery formula described above).

[WarrenBelz]

Hi @Laela ,

Happy to a explore a solution if you need to use your methodology, but I will make a suggestion first. If the source fields of the calculated column are available in PowerApps, why not do the lookups there?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Laela]

Hi @WarrenBelz 

Thank you for your reply. Please elaborate on what you meant by if the source fields are available in Power Apps?

Just to give more context:

• I have a Power Apps Edit Form on a screen where a user will input a date. An expiry date needs to be automatically calculated from that to be 4 years away from the user input date, and saved in my SharePoint List as an "Expiry Date". 
• I have a gallery on a separate screen where "Expiry Date" data will be filtered to only show "Expiry Dates" that are 6 months away or less. The formula for this is not working though. 

Regards,

Laela

[WarrenBelz]

Thanks @Laela ,

I am referring to the fact SharePoint Calculated Columns are based on other fields in the data set which PowerApps also has vision of so the calculation

DATE(YEAR([User Input Date])+4,MONTH([User Input Date]),DAY([User Input Date]))

could easily be done in Power Apps as 'User Input Date' is available so

DateValue(
   Month(DateValue('User Input Date'.Text))
    & "/" & 
   Day(DateValue('User Input Date'.Text)) 
   & "/" & 
   Year(DateValue('User Input Date'.Text)) +4
)

will get the calculated field value you did in SharePoint

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Laela · Accepted solution]

Hi @WarrenBelz 

Thank you for your suggestion. 

I managed to figure out a way to do it with the SharePoint calculated column though. 

I did it this way just in case a user decides to update the User Input Date from the SharePoint List, because they will have access to edit the SharePoint List as well. 

My Solution is:

In my SharePoint List calculated column, I had the following formula to make the expiry date 4 years ahead of the User Input Date (I added the ISBLANK in case a user does not input a date):

=IF(ISBLANK([User Input Date]),"",DATE(YEAR([User Input Date)])+4,MONTH([User Input Date]),DAY([User Input Date])))

In my PowerApps Gallery, I have the formula to flag dates that are 6 months away from the expiry date:

Filter('Data source', DateValue('Expiry Date') <= DateAdd( Today(), 180 )&&DateValue('Expiry Date',"en-GB") >= DateAdd( Today(), -180 ),!IsBlank('Expiry Date'))

For anyone who is not familiar with it, here is the link to the DateValue page: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datevalue-timevalue

"Converts a date, a time, or both in a string to a date/time value."

Then, if I want to display the date in a label, I have the formula:

Text(DateValue(ThisItem.'Expiry Date'),ShortDate)

Regards, 

Laela

[WarrenBelz]

Hi @Laela ,

A little curious -  you do not need to delete the SharePoint column to do the direct PowerApps lookup - you just don't use it in PowerApps, however I am glad it worked for you.