Display Latest Year from Drop down

[_kikilalaaa]

Hi all.

I have a "Year" dropdown and the Items for it is from SharePoint list. 

In the sharepoint list, the year are not uniques, this means that there might be 2 "2019" in the list. The thing that distinguish them is the modified date. For example:

Year              Modified date

2019             12/12/2019 10am

2019              11/12/2019 10am

2018              11/12/2019 9am

2018              11/12/2019 9.03am

What I want to do is, in the powerapps, the dropdown should only show the latest data for each year. This means that the redult should be like this:

Year              Modified date

2019             12/12/2019 10am

2018              11/12/2019 9.03am

As for now, what I did is:

Drop down Items = Sort(Distinct(Filter('OrderList', Location= varLocation), Year),Result,Descending)

but the items showed the first entry for each year, not the latest one.

Can anybody helps?

[CarlosFigueira]

You can use something like this expression to get only the latest modified date from each year:

AddColumns(
    GroupBy(
        Filter(OrderList, Location = varLocation),
        Year,
        "ByYear"),
    "LatestModified",
    Max(ByYear.'Modified date'))

This will first group the list by years, and later add a new column to the grouping that contains the maximum (latest) value of the modified date.

You can find more information about grouping in the GroupBy function reference.

Hope this helps! 

[v-xida-msft]

Hi @_kikilalaaa ,

Do you want to display the latest "Modified date" for each year within your Dropdown box?

Based on the needs that you mentioned, I think the Distinct function could achieve your needs. As an alternative solution, I think the GroupBy function and AddColumns function could achieve your needs.

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Year Dropdown box to following:

DropColumns(
       AddColumns(
           GroupBy(
                    'OrderList',
                     Year,
                     "GroupData"
           ),
           "ModifiedDate",
           First(SortByColumns(GroupData, 'Modified date', Descending)).'Modified date'
       ),
       "GroupData"
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[_kikilalaaa]

hi @v-xida-msft ,

can you explain your steps?

what is "groupdata" and "modified date"? do i have to create it first manually in the sharepoint? 

[v-xida-msft · Accepted solution]

Hi @_kikilalaaa ,

Based on the issue that you mentioned, I think you have some misunderstanding with GroupBy function.

The "GroupData" is set as the third arguement of the GroupBy function, which is used to store record data not in group column name(s).

The "Modified date" represents the "Modified date" column that you mentioned, which you want to get the latest value. If the "Modified date" that you mentioned is the Modified column in your SP List, please modify above formula as below:

Set the Items property of the Year Dropdown box to following:

DropColumns(
       AddColumns(
           GroupBy(
                    'OrderList',
                     Year,
                     "GroupData"     // GroupColumnName, is used to store the rest data except the "Year" column value
           ),
           "ModifiedDate",
           First(SortByColumns(GroupData, Modified, Descending)).Modified    // Modify formula here
       ),
       "GroupData"
)

Please consider take a look at the GroupBy function, then  try above formula, check if you could understand it properly.

Best regards,