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
Phil_Seamark Super Contributor
Super Contributor

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
Phil_Seamark Super Contributor
Super Contributor

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 156 members 1,754 guests
Please welcome our newest community members: