Label displays number different than formula calculates

[GlennM]

Greetings - hope everyone is having a great holiday season. I'm trying to create an employee performance review application, and it is using two galleries for displaying employees from Office365Users connected to a SharePoint library that contains employee files, including performance reviews. I'm trying to construct an indicator at the employee level if the review for the current year, so I created a collection of the reviews that adds the "Review Year" from the completion date of each performance review. I added a text label with a lookup formula to get the selected employees MAX Review Year from the new collection, but for some employees, the year displayed is one year off. When I look at results in the formula editor the lookup of the MAX Review Year is 2022, but what is displayed in the label on the canvas is 2021.  It appears to only happen to some employees, even though they all are in the same document library and use the same Completion Date column. I have refreshed the connection to the library without impact.  I'm hoping someone can help me solve this mystery.  Thanks in advance.

[WarrenBelz]

Hi @GlennM ,

Can you please share your formula in Text and also how many documents are in your Library and your Delegation limit.

[GlennM]

Here is the label formula: LookUp(colLatestReview,galleryKey.Text = GalleryName, Max(ReviewYear)) where galleryKey.Text is employee name organized by last name, first name, GalleryName is Last Name First Name extracted from library folder paths.

The basic collection for the Review Library is: ClearCollect(colReviews,Filter('Employee Folder',"Performance Review" in 'Document Type'.Value));

The collection that adds review year to colReviews is:  ClearCollect(colLatestReview,AddColumns(colReviews,"GalleryName",Mid('Folder path',Find("/",'Folder path')+1,Find("/",'Folder path',Find("/",'Folder path')+1)-Find("/",'Folder path',Find("/",'Folder path'))-1),"ReviewYear",Year('Date Completed')));

The Employee Folder library itself has a significant number of documents, but the collection filters to only performance reviews, which currently total 302.  I have the data row limit set to 2000.  Hope that helps. 

[WarrenBelz · Accepted solution]

Hi @GlennM ,

What "level" folder are your trying to find ? If for instance you wanted the third level, you just need this

Index(
   Split(
      'Folder path',
      "/"
   ).Result,
   3
).Result

but back to your code - you can make this collection - assuming 'Document Type' is a single select

With(
   {
      wPR:
      Filter(
         'Employee Folder',
         'Document Type'.Value = "Performance Review"
      )
   },
   ClearCollect(
      colLatestReview,
      AddColumns(
         wPR,
         "GalleryName",
         Index(
            Split(
               'Folder path',
               "/"
            ).Result,
            3
         ).Result,
         "ReviewYear",
         Year('Date Completed')
      )
   )
);

then your Label

LookUp(
   Sort(
      colLatestReview,
      ReviewYear,
      Descending
   ),
   GalleryName = galleryKey.Text
).ReviewYear

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

[GlennM]

So you really are a super user, that did the trick. I still have a lot to learn about formulas. Thanks for the help!