Sort dropdown month Year Date value with newest month year first. Sort order in Items is not working

[pcoombe]

Hi Everyone

I am perplexed and would love some assistance. My dropdown contains a month and year value calculated by a variable from the OnStart properties. I have the following formula in the ITEMS section of a dropdown box which should work. 

Sort(Distinct(SPcolBlanceSheetDate,DateValue),SortOrder.Descending)

However the date still defaults to the oldest mot / year as opposed to the latest. i cannot work out why the sort option on the dropdown is not working. In screenshot 2 you'll see the Onstart variables. 

[WarrenBelz]

Hi @pcoombe ,

Firstly DateValue is a very bad name for  field as it is a Reserved Word in Power Apps and you have a potential Ambiguity issue there. Also what type of field is it and in what format is the data ?

[pcoombe]

Hi, It is a dropdown field using a variable reference SPcolBalanceSheetDate

[WarrenBelz]

@pcoombe ,

What is your Data Source type and where are you getting the values of Month and Year from ?

ClearCollect(
   SPcolBlanceSheetDate,
   AddColumns(
      'Balance Sheet Reconciliation',
      DateValue,($"{Month} {Text(Year)}")
   )
);

[pcoombe]

Hi Warren

Datasource type is SharePoint. Both Month and Year are text values on the SharePoint site. 

[WarrenBelz]

@pcoombe ,

Your problem is that Power Apps cannot convert the String "September" (or any other month) into a value it can sort in calendar order. Why don't you simply store it as the first of every month in a Date field. You can get the month name back with 

Text(DateField, "mmmm")

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[pcoombe]

Hi

Nice idea but that doesnt work either. I got initally very excited when I tried this with Created Date and it worked a treat. 

ClearCollect(SPcolBalanceSheetDate,AddColumns('Balance Sheet Reconciliation',DateValue,($"{Text(Created,"mmmm")} {Text(Year)}")));

So as per your recommendation I then created a brand new Date/time field called BalSheetDate ; then updated my OnStart ClearCollect to ClearCollect(SPcolBalanceSheetDate,AddColumns('Balance Sheet Reconciliation',DateValue,($"{Text(BalSheetDate,"mmmm")} {Text(Year)}")));

 but nothing happened. the  sort order remains old to new. I even tried chaning my browser settings from UK to US and back. Makes no difference. 

[WarrenBelz]

@pcoombe ,

 The point is that you need to sort by an actual date column, even if it is in the background and not the field displayed in the control.

[pcoombe]

Hi Warren 

Unfortuantely no matter what I do I still cannot get the sort order to work. You can see below the original OnStart formula and then 3 commented out attempts. The field value CreatedDatePriormonthFirst is a formula field I created in SharePoint that automatically takes the created date and applies a date of the 1st of the month of the prior month. You can see below in SharePoint where I have Created and then 2 further fields I createed (balSheetDate which is a manual entry) and the CreatedDatePriotMonthFirst which is as I say a formula. 

Regardless of the onstart command I use this is what I always have i.e. September 2023 when the newest value is March 2024. I'm really stuck

[WarrenBelz]

Hi @pcoombe ,

Firstly DateValue is a very bad name for  field as it is a Reserved Word in Power Apps and you have a potential Ambiguity issue there. Also what type of field is it and in what format is the data ? I cannot add much more to what I posted before - you need an actual date field in the Items structure if you want to sort in that way.