Forum Discussion
sorting by month (text)
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 Your calculation should be:
=YEAR([xxx Day]) * 100 + MONTH([xxx Day])
Set this as your Sort By column of your text month name.
7 Replies
Anonymous Your calculation should be:
=YEAR([xxx Day]) * 100 + MONTH([xxx Day])
Set this as your Sort By column of your text month name.
Greg_Deckler Why multiply * 100
Anonymous See previous reply.
Greg_Deckler why multiply by 100
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.