Forum Discussion
Convert numbers to month
Hi everybody,
I have a column with months dates in numbers. I want to convert it to text and keep the monthly order as Jun, Feb, Mar, Apr, etc..
I tried several trick like grouping or entering a new table to edit relationships, however in the graph the months are sorted alphabetically. I also tried the formula Months = text('date'[Month],"MMM") but still doesn't work.
Any help?
Cheers,
Andrea
Hi Anonymous,
The month column is RS month. I need to convert thos numbers in text as Jan, Feb, Mar, etc. but keeping the monthly order when showed in a graph.
In this scenario, you can use the formula below to create a new calculate column to convert the numbers in text as Jan, Feb, Mar, etc. :smileyhappy:
Short Month = SWITCH ( Table1[RS Month], 1, "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec", BLANK () )To keep the monthly order for the "Short Month" column, you can use the 'Sort by Column' option under Modeling tab.
- Select "Short Month" column.
- Click 'Sort by Column' option under Modeling tab.
- Select "RS Month" column.
Regards
9 Replies
Hi Anonymous
use format instead of text...
Month short = FORMAT('date'[Month],"MMM")matt mattson has this blog post
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
use some of the logic here and do it in power query , you might have to convert then convert again.
and then in modelling in power bi set your name column to sort by the number.
Not sure if this will work for you but seems most of the solutions here are overly complex.
try this formula
format(date(1,'date'[Month],1),"MMM")
