How to get the months in order in a collection

[OsmandFernando]

Hi

@PowerAddict and other experts

I have the following collection. But the month showing not in order. I want it to have months in order

I have linked this collection to a chart, so months are not showing in order in the chart

How Can I achieve this ?

Osmand

[yashag2255]

Hey @OsmandFernando 

Direct sorting based on Month name is not doable directly. As a workaround to this, you can set the configuration as:

App -> OnStart -> ClearCollect(MonthName,{Month:"January",value:1},{Month:"February",value:2},{Month:"March",value:3},{Month:"April",value:4},{Month:"May",value:5},{Month:"June",value:6},{Month:"July",value:7},{Month:"August",value:8},{Month:"September",value:9},{Month:"October",value:10},{Month:"November",value:11},{Month:"December",value:12})

Note: Make sure that you run OnStart again so that the collection is populated in your current edit session.

Now, Chart -> Items ->
Sort(AddColumns(CollectionName,"MonthOrder",LookUp(MonthName,Month = SalesMonth).value),MonthOrder,Ascending)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-monli-msft]

Hi @OsmandFernando ,

You actually do not need to directly get the months in order for the data inside the collection, you can sort the collection with SortByColumn function and use it in the Items property of your Chart control.

For example, you can use a formula like below in the Items property of a Chart control:

SortByColumns( Collection1, SalesMonth, Ascending )

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

For more information about this function, please refer to:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

Regards,

Mona

[OsmandFernando]

Hi @v-monli-msft Thank you so much for your reply

I tried as follows, 

If(Dropdown2_1.Selected.Value="All",SortByColumns(SalesSumbyMonth,"SalesMonth",Ascending),Filter(SalesSumbyMonth, Text(Value(SalesYear), "[$-en-US]###") = Text(Dropdown2_1.Selected.Value)))

But still Jan is not in the correct place also it starts from right to left

[OsmandFernando]

@v-monli-msft 

@yashag2255 

@mdevaney 

Anyone can help me here ?

Osmand

[OsmandFernando]

@yashag2255  brother, i tried your solution. But it did not change the order

following the chart item property. MonthName collection also created and visible 

If(Dropdown2_1.Selected.Value="All",Sort(AddColumns(SalesSumbyMonth,"MonthOrder",LookUp(MonthName,Month = SalesMonth).value),MonthOrder,Ascending),Filter(SalesSumbyMonth, Text(Value(SalesYear), "[$-en-US]###") = Text(Dropdown2_1.Selected.Value)))

Osmand

[mdevaney]

@OsmandFernando
Personally I would take the lazy approach here and change the month names from this...

January, February, March

To this...

01 - January, 02 - February, 03 - March

From there you can just do a simple SORT on month name column

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

[OsmandFernando]

Thank you for your suggestion 

@mdevaney 

Osmand

[yashag2255 · Accepted solution]

Hey @OsmandFernando 

Can you please confirm if data exist in the collection? Since the expression is written on the "OnStart" property of the app, this will load the collection when the app is opened/ refreshed. It will not fill the data in current edit mode until it is done manually. You can check the collection by navigating to:
View -> Collections -> MonthName

On the left menu, select the three dots after App on the left menu and select "Run OnStart". This will populate the collection.

According to the expression, sort will only be applied if the dropdown selection is "All". If you want to sort this no matter the dropdown selection, in that case, update the expression to:

If(Dropdown2_1.Selected.Value="All",
Sort(AddColumns(SalesSumbyMonth,"MonthOrder",LookUp(MonthName,Month = SalesMonth).value),MonthOrder,Ascending),
Sort(AddColumns(Filter(SalesSumbyMonth, Text(Value(SalesYear), "[$-en-US]###") = Text(Dropdown2_1.Selected.Value)) ,"MonthOrder",LookUp(MonthName,Month = SalesMonth).value),MonthOrder,Ascending))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[OsmandFernando]

When I filter by year now it comes correctly

@yashag2255 

Thank you all

Osmand