sorting by month (text)

[Anonymous]

ssas tabular model - I have a month column, which is a text datatype, and is based on a day column which is date datatype

I have a power bi report doing live connection to this model, when I try to put the month column on x axis on a visual, it sorts according to a text, how to make it sort like a number ?

I tried doing =YEAR([xxx Day])&MONTH([xxx Day]), and keep the data format general and data type whole number, it stores data ase 20228 (instead of 202208)

the minute I change both data format and data type to whole number at the ssas cube, power bi sums it up , i cannot use summarization, how to solve this problem

[Anonymous]

@Greg_Deckler  why multiply by 100

[Greg_Deckler]

@Anonymous It gives you the 0 for single digit months. 2022 * 100 = 202200 + your month number results in 202201, 202202, 202203, 202204, 202205, 202206, 202207, 202208, 202209, 202210, 202211, 202212 and thus this provides a consistent sorting by year and month.

[Greg_Deckler · Accepted solution]

@Anonymous Your calculation should be:

=YEAR([xxx Day]) * 100 + MONTH([xxx Day])

Set this as your Sort By column of your text month name.

[Anonymous]

@Greg_Deckler  Why multiply * 100

[Greg_Deckler]

@Anonymous See previous reply.

[Anonymous]

@Greg_Deckler  so I added a new column with above calculation in the msbi tabular model

year(day_column) * 100 + month

and data format General, data type whole number, the power pivot shows correct values but power bi sums it up and shows wrong values 

[v-jingzhang]

Hi @Anonymous 

This thread may be helpful: https://stackoverflow.com/questions/70458737/sort-a-calculated-column-ssas-tabular-model . After creating the new whole number data type column in the tabular model, select the original text data type Month column and set the Sort By Column property to the new whole number data type column. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.