Answered

Add calculated field in Power App

(0) Share

Report

Posted on by StaceyGriffeth

I have a Sharepoint List data source for a Power App. The list is of contractors. Two of the fields in the list are Start Date and End Date. In the Power App, I would like to add a calculated field to determine the Status by comparing the Start Date and End Date to Today, e.g.

=IF([Start Date] > TODAY, "Inactive",
IF(AND([Start Date] < TODAY, [End Date] > TODAY), "Active",
IF(AND([Start Date] < TODAY, [End Date] < TODAY), "Inactive",
IF(ISBLANK([Start Date]), "Inactive",
"Active"))))

But I haven't been able to determine where to add the calculated field in Power Apps. Can anyone advise? Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @StaceyGriffeth

In the Gallery where you're displaying the contractor data, add a Label or Text Input control. This will act as your calculated field to show the status.

Set the Text property of this Label or Text Input control to perform the calculation based on the Start Date and End Date fields from your SharePoint list.

Text(
If(
IsBlank(ThisItem.'Start Date'),
"Inactive",
If(
ThisItem.'Start Date' > Today(),
"Inactive",
If(
And(
ThisItem.'Start Date' < Today(),
ThisItem.'End Date' > Today()
),
"Active",
If(
ThisItem.'End Date' < Today(),
"Inactive",
"Active"
)))
))

(Or)

If(
ThisItem.'Start Date' > Today(), "Inactive",
ThisItem.'Start Date' < Today() && ThisItem.'End Date' > Today(), "Active",
ThisItem.'Start Date' < Today() && ThisItem.'End Date' < Today(), "Inactive",
IsBlank(ThisItem.'Start Date'), "Inactive",
"Active"
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

Was this reply helpful? Yes No
StaceyGriffeth 76 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the quick reply, @Rajkumar_404 ! This would be displayed in a data table, and it would calculate for each row in the table. How do I add a text label or input for each row in the table? I apologize if this is elementary, I'm very new to Power Apps.

Was this reply helpful? Yes No
Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!

This is for only display calculate data in PowerApps. If you want to store displayed data to SharePoint, use the Patch function. To display SharePoint data, use Gallery.

Was this reply helpful? Yes No
StaceyGriffeth 76 on at

Like (0)

Report
Copy link

Link copied!

Hi @Rajkumar_404 I converted this to be a gallery, added all of my fields, and created the calculated field 'Status'. It is calculating correctly except where Start Date is blank. Those items are returning a value of "Active". Start Date is a 'Date and Time' field in the Sharepoint list. The formula for the Status calculation is:

If(
ThisItem.'Start Date' < Today() && ThisItem.'End Date' > Today(), "Active",
ThisItem.'Start Date' < Today() && IsBlank(ThisItem.'End Date'), "Active",
ThisItem.'Start Date' < Today() && ThisItem.'End Date' < Today() && Not(IsBlank(ThisItem.'End Date')), "Inactive",
ThisItem.'Start Date' > Today(), "Inactive",
IsBlank(ThisItem.'Start Date'), "Inactive",
"Active"
)

Do you have any idea why that would not be calculating correctly?

Was this reply helpful? Yes No
Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @StaceyGriffeth

You can modify the formula to check if the 'Start Date' is blank before evaluating its value.

If(
IsBlank(ThisItem.'Start Date'), "Inactive",
ThisItem.'Start Date' < Today() && ThisItem.'End Date' > Today(), "Active",
ThisItem.'Start Date' < Today() && IsBlank(ThisItem.'End Date'), "Active",
ThisItem.'Start Date' < Today() && ThisItem.'End Date' < Today() && Not(IsBlank(ThisItem.'End Date')), "Inactive",
ThisItem.'Start Date' > Today(), "Inactive",
"Active"
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

Was this reply helpful? Yes No
StaceyGriffeth 76 on at

Like (0)

Report
Copy link

Link copied!

@Rajkumar_404 that fixed it! Thanks so much for all your help!

Was this reply helpful? Yes No