Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    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")
        )
    )

     

Ahmedx's avatar
Ahmedx
Super User
2 years ago

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")
        )
    )

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Ahmedx ,

 

Thanks a lot for your response, it worked perfectly!