Forum Discussion

jj14x's avatar
jj14x
Advocate II
9 years ago
Solved

sort date

Hi! I'm relatively new to Power BI, so I hope I'm just overlooking something obvious.   I have  a view that I import from SQL Server into Power BI. In that view, there is a date field, and I need ...
  • malagari's avatar
    9 years ago

    Currently, the only way to do what you're asking is to create another column for the month, at least on a Table visualization.  On a line chart, you're able to sort each drill-down step its own way.

     

    I agree that ideally the automatic hierarchy of DateTime values should allow sorting for each group, but that's not the case right now.

  • Phil_Seamark's avatar
    9 years ago

    Do you always want your table to be sorted Year Ascending and Month Desc, and not to allow users to override this?

     

    If so, you'll need to add a Date table to your model (if you already haven't) and add two columns to this table.  The first for your Month and the 2nd for sorting your months.  You'll also need to relate your Date table to your main table.

     

    To add a Date table to Power BI click the New Table button and add this code

     

    Dates = CALENDARAUTO()

    Then add these three columns to the above table

     

    Month = FORMAT('Dates'[Date],"MMMM YYYY")
    Month ID = 0 -INT(FORMAT('Dates'[Date],"YYYYMM"))
    Year = YEAR('Dates'[Date])


    Be sure to specify that the [Month] column is to be sorted by the [Month ID] column.

     

    Then you can add [Year] & [Month] to your table.  You can Sort [Year] up and down but [Month] should default to Desc