sort date

[jj14x]

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 to bucketize number of rows by year/month. Should be simple enough.

I created a new table visualization, and added the date field to "Values". It automatically bucketized by Year/Qtr/Month/Day - I deleted Qtr and Day, and am left with Year/Month. Summarization is correct. It sorts in ascending (old year/month at top). This works ok.

I need to sort it descending though. When I click on year, it puts the year in descending order, but the month is still in ascending order. Is there a way to sort the year and month together?

(I realize I can add a new column with year/month, and sort that - but that 'should' not be needed)

Thanks!

[malagari · Accepted solution]

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 · Accepted solution]

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

[malagari · Accepted solution]

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.

[jj14x]

Thanks malgari & Phil. Hopefully the Power BI team will be adding this feature soon.

Phil, I wanted the default view to show up in descending order - but allow the users to change it if they wanted to (they change on their power BI view, and it resets when they log in the next time). I do have the date table built (for working day calculations), so that helps.