Problems Sorting a text column from a Sharepoint list

[fyoshida]

Hello everyone,

I am getting crazy trying to figure it out this, and I hope you can help me on that.  I have a poweapp that gets data from a Sharepoint list, in this SP I have a column that convert date to a specific text format (ie.: MMM YYYY) this SP List Column convert dates using the formula =text(date, "MMM YYYY"). When I use this field as a filter on my app, I cannot get this info properly sorted.

Nov 2019

Dec 2019

Sep 2019

May 2020

Jan 2020

Feb 2020

Mar 2020

Apr 2020

I am using the following syntax:

Sort(Distinct('CDatabase', MonthYear), Descending)

I used Distinct once I have several lines per month and I don´t want them duplicated on the menu.

If I use Sort(Distinct('CTUX Executive Dashboard', MonthYear),Result)

then i got in alphabetical order,

Apr 2020,

Dec 2019

Feb 2019

Jan 2020

Mar 2020

May 2020

Nov 2019

I need to sort the info by date order ie: Nov 2019, Dec 2019, Jan 2020, Feb 2020, etc

any idea on what I am missing here?

thank you everyone in advance

Fabio

[eka24]

No errors in the formula. Can you make a bigger screenshot. I can't see where you put the formula

It the formula put on a Gallery or a dropdown ? It should be on Items of the Gallery

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[fyoshida]

Here the screens

No errors are showed, but in the filter date no info is showed as well

[fyoshida]

updating... I saved the app, closed and reopened. Now I can see a list from 1, to 12 . Is it possible to show as "month year or year month"?

and now the second filter "Region" is not showing any data .

I tried to replace MonthYear by MonthSort on the second filter, but no data is showed also

[fyoshida]

I had put that formula on the second filter in order to, once we chose th month, the second filter will show only the Regions that had items on that month, and the same for the third filter, showing locations only for the regions remaining.

For the galeries I used this:

// All-ALL Selection
If(DDApp.Selected.Result="All" And DDApp1.Selected.Result="All" And DDApp2.Selected.Result="All", 'CTUX Executive Dashboard',
//MonthYear Selection
DDApp.Selected.Result<>"All" And DDApp1.Selected.Result="All" And DDApp2.Selected.Result ="All",
Filter('CTUX Executive Dashboard', MonthYear=DDApp.Selected.Result),
//MonthYear & Region Selection
DDApp.Selected.Result<>"All" And DDApp1.Selected.Result<>"All" And DDApp2.Selected.Result ="All",
Filter('CTUX Executive Dashboard', MonthYear=DDApp.Selected.Result And Region=DDApp1.Selected.Result),
//Region & LOC/AREA Selection
DDApp.Selected.Result="All" And DDApp1.Selected.Result<>"All" And DDApp2.Selected.Result <>"All",
Filter('CTUX Executive Dashboard', 'LOC/AREA'=DDApp2.Selected.Result And Region=DDApp1.Selected.Result),
//MonthYear & LOC/Area Selection
DDApp.Selected.Result<>"All" And DDApp1.Selected.Result<>"All" And DDApp2.Selected.Result ="All",
Filter('CTUX Executive Dashboard', MonthYear=DDApp.Selected.Result And 'LOC/AREA'=DDApp2.Selected.Result),
//LOC/Area Selection
DDApp.Selected.Result="All" And DDApp1.Selected.Result="All" And DDApp2.Selected.Result <>"All",
Filter('CTUX Executive Dashboard', 'LOC/AREA'=DDApp2.Selected.Result),
//Region Selection
DDApp.Selected.Result="All" And DDApp1.Selected.Result<>"All" And DDApp2.Selected.Result ="All",
Filter('CTUX Executive Dashboard', Region=DDApp1.Selected.Result),
//MonthYear & Region & LOC/AREA Selection
DDApp.Selected.Result<>"All" And DDApp1.Selected.Result<>"All" And DDApp2.Selected.Result <>"All",
Filter('CTUX Executive Dashboard', MonthYear=DDApp.Selected.Result And Region=DDApp1.Selected.Result And 'LOC/AREA'=DDApp2.Selected.Result)
)

____

[eka24]

Change the Onstart formula to:

ClearCollect(MyDashboard,AddColumns('CTUX Executive Dashboard',"MonthSort",

Text(Date,"mmm-yyyy")&" "&"MonthNo:"&Month(Date)))

Maintain the other formula:

Sort(Distinct(MyDashboard, MonthSort),Result)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-bofeng-msft · Accepted solution]

Hi @fyoshida :

Do you want to sort by the actual date represented by the string?

I think the key is to convert the string into date type.

I've made a test for your reference：

My data source(CDatabase):

Collect(CDatabase,{MonthYear:"Apr 2020"}
,{MonthYear:"Dec 2019"}
,{MonthYear:"Feb 2019"}
,{MonthYear:"Jan 2020"}
,{MonthYear:"Mar 2020"}
,{MonthYear:"May 2020"}
,{MonthYear:"Nov 2019"})

Add a Gallery and set it's Items property to :

Sort(Distinct(CDatabase, MonthYear), DateValue(Result),Ascending)

The date text has been arranged in the correct order.

Best Regards,

Bof

[fyoshida]

Hi,

I tried the last syntax and it does not worked

by some reason it does not sort properly, also when I try to link my other filter to this MonthSort I got an error, the same cannot be found.

[fyoshida]

it worked perfectly, and the best was that I haven´t to change anything on the other filters and galleries.

the only thing is that I need to add every new month to the collection, right?

thank you so much

Fabio