Answered

CountRows and Filter SharePoint list by choice field and date field by year equal to year now

(0) Share

Report

Posted on by 3MTeam

I have found a few posts in this community that explain comparing date fields from SharePoint to PowerApps dates but nothing that could explain why the following formula is not getting the correct count based upon my filter in a SharePoint list view:

Set(
 varCountRowsClosed,
 CountRows(
 Filter(
 RFS,
 HiringJobStatus.Value = "Closed" || HiringJobStatus.Value = "Closed - No Bid" || HiringJobStatus.Value = "Closed - Lost Bid" && Year(Text("Request_x0020_Date",ShortDate)) = Year(Text(Now(),ShortDate))
 )
 )
);
Set(
 varAvgDaysUntilClosed,
 Sum(
 RFS,
 DaysUntilClosed
 )/varCountRowsClosed
)

Request_x0020_Date is a date/time column in SharePoint online list and Now is in PowerApps. The HiringJobStatus column is a choice field which shouldn't be what mis calculating. In the second variable set, varAvgDaysUntilClosed, the DaysUntilClosed column is a calculated column. But the first variable I am setting, varCountRowsClosed, with the Count Rows fx is calculating 441 rows when there should only be 21 according to the count in my view. I have made so many changes and cannot get it to jive. Any help is much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,386 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @3MTeam ,

Try the below - there is some bracketing required on and and/or and you do not need to convert dates to Text to get the Year.

With( { wCountClosed: CountRows( Filter( RFS, ( HiringJobStatus.Value = "Closed" || HiringJobStatus.Value = "Closed - No Bid" || HiringJobStatus.Value = "Closed - Lost Bid" ) && Year(Request_x0020_Date) = Year(Now()) ) ) }, Set( varAvgDaysUntilClosed, Sum( RFS, DaysUntilClosed ) / wCountClosed ) )

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
3MTeam 12 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Thank you so much. I appreciate your in depth knowledge. It was the parentheses that I somehow missed. For anyone looking at this for a solution, this worked but I wanted a global variable instead of a context (local) variable so I used the Set function instead of With {wCountClosed: x}. The reason is that I wanted to see the value of my variable in my gallery and I could not see it there with it being a local variable in the OnStart property of the app.

Was this reply helpful? Yes No