cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jj14x Frequent Visitor
Frequent Visitor

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 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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
malagari Member
Member

Re: sort date

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.

Dan Malagari
Consultant at Headspring

View solution in original post

Highlighted
Microsoft Phil_Seamark
Microsoft

Re: sort date

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
malagari Member
Member

Re: sort date

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.

Dan Malagari
Consultant at Headspring

View solution in original post

Highlighted
Microsoft Phil_Seamark
Microsoft

Re: sort date

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

jj14x Frequent Visitor
Frequent Visitor

Re: sort date

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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors