Forum Discussion
Order Segments by Date
Hi all,
I am trying to order my months chronologically but power bi only orders them alphabetically, which is not ideal for a reporting... How can I manage to have them sorted out the way I want to?
For context, I used a calendar table with a first column being all the dates. The second column is formated in order to have the month (text) and the year (all of this with the FORMAT function). Here, we can see the second column.
Thank you for your help,
Best regards.
create a calendar it will be easier for you to sort the Year Month column by the Year Month Number column
Calendar = VAR BaseCalendar = CALENDAR ( DATE ( YEAR(MIN('Orders'[Order Date])), 1, 1 ), DATE ( YEAR(MAX('Orders'[Order Date])), 12, 31 ) ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT ( BaseDate, "mmm yy") ) )
2 Replies
- Ahmedx
Super User
create a calendar it will be easier for you to sort the Year Month column by the Year Month Number column
Calendar = VAR BaseCalendar = CALENDAR ( DATE ( YEAR(MIN('Orders'[Order Date])), 1, 1 ), DATE ( YEAR(MAX('Orders'[Order Date])), 12, 31 ) ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT ( BaseDate, "mmm yy") ) )