This was extremely helpful. Exactly what I was looking for. Power BI handles things differently which makes it bit difficult to understand. Even for the simplest of the things it has long winded ways of doing.
Bless your heart, Adam! For those who are looking for "Modeling" in this tutorial, it's now (July 2022) under Column Tools. Starting to get frustrated with how much they change these tool names and positions every month!
This video is a godsend. I just spent my entire work day trying to figure out how to sort my slicer data according to its number (1 is followed by 10 instead of 2) Im so glad that hell is over
Thanks for this! Very helpful! However, I wish MS will create a feature (a button maybe?) that will make arranging month and year chronologically easier.
Thanks a lot brother .. I have been trying to get this thing sorted out for more than 2 hours over the internet but none worked until I came across your video .. you a are a legend :D
Hey ! Do you know a way to do the same thing, but only with the first character of the month ? When I try to do so in the report the months which started with "J" are combined into 1 "J" month in the chart, and the others are seems without data (blanks). Thank you !
lol, I had to watch this again because it's been a month and half since I watched it the first time! (I'll remember it this time) Thanks to you guys, I'm building some really cool reports for my company! You guys rock!
2:20 How about if we want a running timeline that goes from December 20 21 to January 2022? My data collection starts in October so we have October 2021, November December January 2022. What’s the best way to make January appear after December because the year has changed and it’s the following year? Thanks in advance!
You can follow this same exact procedure BUT the month number for October onwards needs to be so Oct - 91, November 92, December 93, January 94. It feels stupid but the program reads the 1's in October - December as 1 and not 10, 11, 12. Hope this helps!
Really cool, trick. In my work I have to work with a corporate calendar running from September to August, so what i did for the same reasons was create an extra table, through Enter data, to assign the month numbers to the months based on our calendar and then create the relationships between the table to sort based on our unique month number. Same technique then works perfectly
Thank you for the Video. I have a question regarding bar charts but not so much on sorting. Are custom clustered stacked bar charts available in power bi. I want to have a stacked column splitting to categories and then next to that column I want the column reflecting the total, any ideas? is it available?
Thanks, Adam! Finally a solution to this aggravation! Now I can put away the hemlock and the hary-kary knives, at least for now! You guys are da bomb! Get cubed!!
Thanks for th quick tip. Is there any way to transfer this configuration (also the hierarchies created) to another pbix? or it has to be built manually each time?
Hey Adam how do you sort a filter pane with month name column in chronological order. So when a user is filterering then can see all months in right order
But tricky question is how do we show the month as a number, instead of text string? Any date value has automatic year, quater, month, date values, but sadly I haven't figured out how to format the month to show properly as 01 - 12, instead of January - December. Seems odd that the default is text instead of number.
From here, the next progression, that always puzzles is preserving custom calendar based visuals. E.g. where you want to present by year initially, but offer drill-down from that into either a (Fiscal) Semester (3 periods of 4 months) or quarter, then to month, week, then finally, individual day. Commonly trying to drill-down from year to semester, three periods are presented containing the total of each for all years, rather than breaking-down the corresponding years into their constituent semesters in time series. This would be an interesting topic to cover-off, one step removed from the chronological sort.
Hi, I have continuous data , chart build in line chart for 2 different years for all dates. Doing so, it does align the months in sorting order from January but changes my data from continuous to categorical. Could you please suggest to keep the chart continuous and sorting months from January. Also, how to create bins of no. of days since we cant represent all dates on x-axis. Thanks in advance!
Hi, i have dates from April 2018 to December 2019, i want to show the line chart/ matrix column , sorted by month descending as Dec 2019 back to April2018
Now how do I do this if I'm wrapping the previous year? I've got my month names set up like Dec 18 and Dec 19, but having trouble getting Dec 18 to the far left and Dec 19 to the far right. Any thoughts?
It reminds me that I did it in Tabular model inside visual studio. Since edit query is not available for live connection in Power BI. This trick is really cool. I used it for fiscal year as well.
Thanks so much. I have a question. Can you explain how the same sorting can be done in the DIRECT QUERY OPTION? because the direct query does not have this type of sorting technique. Thanks.
Sort by column has been removed from the Modeling Tab (still available in the Data View) in the August 2020 update... DO you know if it has been relocated somewhere else. I'm unable to put my months in order now. My girlfriend has the view on her work computer because they haven't updated versions yet.
This is a good solution within one year. But what is the appropriate solution for time hierarchy based on a specific date (not a month string)? I want to expand all down one level (e.g. 2018 Q1 to 2020 Q3) and it does not sort properly. Do you have a solution for that?
Quick question. I working off of a direct query from a table in SQL and I want to sort by grade (k-12). I created a gradeSort column where k=0 and first = 1 up through 12 grade. When I set the chart to sort by gradeSort it works until I clear my slicer. The chart reverts to sort ascending where grade K is the last grade on the chart. When I go to the table and set the column Grade (in advanced properties) to sort by gradeSort I get a circular reference error. I know I could create the column gradeSort in SQL and it would be included in my direct query, but I am stubbornly trying to solve this in Power BI. Thanks!
For direct query connections to SQL server, this doesn't seem to work, I have both weekday and weekday # in my SQL but have to resort to having weekday # on the axis of my charts to get the days sorted correctly.
Hi Adam, nice video, but the inconvenience is that I can't add a calculated column based on the column I want to sort and then sort the column by the calculated column, which would pop up something saying I can't do that. I usually have to add the order column manually by creating a new two-column order table and import the order column from that table by Power Query, is there a better way to do this?
Equate the column you want to sort to a calculated column e.g. 'column you want to sort' = 'new calculated column'. Now create an ID/Num column that will sort the original column you want to sort. Now sort the 'New Calculated column' using this new ID. I believe that will work. Give it a try.
Thanks, but I am not sure I understand, in short, the problem is how to create an "ID/Num" column，if you you create it as a calculated column based on the column you want to sort, Power BI will not accept it as a sort standard for there is circular reference or something.
I know about the trick for many Weeks and I just tried it last Week but it did not work do you know why, because I was trying to sort on a column having multiple values for the same month (like a one to one relation between the two columns). So thank you for sharing.
2 роки тому
Nice video. Wait can I do the same thing if I want the months to start in July? In my company the fiscal year is from July - June.
This is very good . I'm still looking for the tips to sort the data by financial quarter and month. I also would like to show the data last rolling 12 months. Any help would be appreciated.
This was extremely helpful. Exactly what I was looking for. Power BI handles things differently which makes it bit difficult to understand. Even for the simplest of the things it has long winded ways of doing.
Bless your heart, Adam! For those who are looking for "Modeling" in this tutorial, it's now (July 2022) under Column Tools. Starting to get frustrated with how much they change these tool names and positions every month!
This was super helpful. I didn't need this for month name but was able to apply it to my table. Thank you!
This video is a godsend. I just spent my entire work day trying to figure out how to sort my slicer data according to its number (1 is followed by 10 instead of 2) Im so glad that hell is over
You guys are great. Not only do you teach, you're entertaining. Keep doing what you are doing. #LoveAndRespect
I didn't know and was struggling with exactly that issue!! Thanks!! You guys rock!!
Great job! Sometimes solutions are easy, but you can't get it. I struggled a lot with this and came to youtube. Got the solution. Thank you 🙂
Happens to all of us. Glad it worked out for you 👊
Simple and extremely helpful. Thank you! Love this channel.
I was looking for this solution for three days! BIG THANKS!
This is so sick, extremely useful exactly what I needed CHUR
Thanks for this! Very helpful! However, I wish MS will create a feature (a button maybe?) that will make arranging month and year chronologically easier.
Wow! I spent hours trying to figure this out. Thank you so much for this video!
Awesome tip! Thanks for sharing.
Thank you....it might look simple but i struggled with it before i bumped into your video....Much appreciated.
Awesome, this what I have been looking for exactly. Thanks for the tip
Thanks a lot brother .. I have been trying to get this thing sorted out for more than 2 hours over the internet but none worked until I came across your video .. you a are a legend :D
FINALLY ! Took me 3 days to find this answer. You made my day ! Thanks.
Great tip!! I found this very helpful
Amazing! Simple solution to something I thought I was going to have to develop manually. Thanks for the simple tutorial
Thanks for saving me from pulling out my hair! Many blessing to you and your team.
Just learning PBI - and, yes, this did help! So glad I came across your video. I'll Subscribe as well. Thank you!
Hey ! Do you know a way to do the same thing, but only with the first character of the month ?
When I try to do so in the report the months which started with "J" are combined into 1 "J" month
in the chart, and the others are seems without data (blanks).
Thank you !
Modeling ribbon is missing in Data and its only available in report section. Please anybody help how do I enable Modeling ribbon in Data?
thank you very much! Such a relief to see this working!
lol, I had to watch this again because it's been a month and half since I watched it the first time! (I'll remember it this time) Thanks to you guys, I'm building some really cool reports for my company! You guys rock!
2:20 How about if we want a running timeline that goes from December 20 21 to January 2022? My data collection starts in October so we have October 2021, November December January 2022. What’s the best way to make January appear after December because the year has changed and it’s the following year? Thanks in advance!
100 % this Does not solve this problem ...
You can follow this same exact procedure BUT the month number for October onwards needs to be so Oct - 91, November 92, December 93, January 94. It feels stupid but the program reads the 1's in October - December as 1 and not 10, 11, 12. Hope this helps!
One of the best explanations I have seen on this topic.
Many thanks for this. This is exactly what I was looking for!
Thank you so much, this resolved the issue without a fuss. Great video
How can you sort by the Day Name (Monday, Tuesday, Wenesday etc..) for the X-Axis? I cannot seem to get this to work.
Wow. Thanks you so much for this amazing tip!
Helped a lot.
Very helpful. Did not know about this feature. It would be helpful to also sort a column in a matrix by any calculated measure.
Really cool, trick. In my work I have to work with a corporate calendar running from September to August, so what i did for the same reasons was create an extra table, through Enter data, to assign the month numbers to the months based on our calendar and then create the relationships between the table to sort based on our unique month number. Same technique then works perfectly
Thanks for this, I was wondering what the best way to assign the month number would be.
I didn't know about this one! I'm going to try it out.
(Love the T-shirt)
tx u so much I struggled for 2 days after seeing this video- its was really quick
Thank you for the Video. I have a question regarding bar charts but not so much on sorting. Are custom clustered stacked bar charts available in power bi. I want to have a stacked column splitting to categories and then next to that column I want the column reflecting the total, any ideas? is it available?
OMG, I cant thank you enough for this! i was putting #'s and letters in front of these for slicers
Thanks, Adam! Finally a solution to this aggravation!
Now I can put away the hemlock and the hary-kary knives, at least for now!
You guys are da bomb!
Get cubed!!
Thanks for this video! I was able to do it without any problems!
Nice and quick tips thanks a lot!
Thanks for th quick tip. Is there any way to transfer this configuration (also the hierarchies created) to another pbix? or it has to be built manually each time?
Thank you so much man, this was really helpful 👍🏽
NIce and Quick, just what I was looking for. - subscribed
Thanks for subscribing! 👊
Thanks a lot, you guys are super helpful
Extremely helpful! Thank you
Hey Adam how do you sort a filter pane with month name column in chronological order. So when a user is filterering then can see all months in right order
Sir, You are absolutely correct sometime i hv done complex calculations but stuck to sort in correct order.
Thanks for sharing.
Take Care
You help me a lot, cause I struggled with this for a long long time !!
"Knowing is half the battle" Liked
Just spent all morning searching the web for this answer and trying different things. Not much of a commententer but this video warranted one.
Is there anyway i can add the month number on my model like adding it manually without redoing ky entire model?
Power BI noob here. This video got me over the hump! Thanks, Adam!
Thank you so much! This tip helped a lot!
But tricky question is how do we show the month as a number, instead of text string? Any date value has automatic year, quater, month, date values, but sadly I haven't figured out how to format the month to show properly as 01 - 12, instead of January - December. Seems odd that the default is text instead of number.
this was exactly what i was looking for thank you
From here, the next progression, that always puzzles is preserving custom calendar based visuals. E.g. where you want to present by year initially, but offer drill-down from that into either a (Fiscal) Semester (3 periods of 4 months) or quarter, then to month, week, then finally, individual day. Commonly trying to drill-down from year to semester, three periods are presented containing the total of each for all years, rather than breaking-down the corresponding years into their constituent semesters in time series. This would be an interesting topic to cover-off, one step removed from the chronological sort.
Hi,
I have continuous data , chart build in line chart for 2 different years for all dates. Doing so, it does align the months in sorting order from January but changes my data from continuous to categorical. Could you please suggest to keep the chart continuous and sorting months from January.
Also, how to create bins of no. of days since we cant represent all dates on x-axis.
Thanks in advance!
Hi, i have dates from April 2018 to December 2019, i want to show the line chart/ matrix column , sorted by month descending as Dec 2019 back to April2018
I also search for that solution but it seems to be not quite trivial
You need to make a new column that sorts month + year (like "202002" for Feb 2020).Then sort the desired column by that column.
You can do it by using switch function and give the April to number 1 and so on i think this will work
@@hansluderer3493 it works. i was also wondering
Thanks for this information
A simple but essential piece of information. Thank you!!!
Now how do I do this if I'm wrapping the previous year? I've got my month names set up like Dec 18 and Dec 19, but having trouble getting Dec 18 to the far left and Dec 19 to the far right. Any thoughts?
This was incredibly useful. Thank you very much Sir!!
Happy to help! Thanks for watching! 👊
This Helped a lot Thanks!
OMG you saviour!!!
Thanks so much for this very simple and important fix!
Simple but useful tip. Definitely helped me.
It reminds me that I did it in Tabular model inside visual studio. Since edit query is not available for live connection in Power BI. This trick is really cool. I used it for fiscal year as well.
Thanks for this very insightful. How would I go about doing this for Quarters also?
Thank you very much for this. Indeed I spent too much time on this problem. Now it is solved!
Thanks , it was so helpful.
Thank you so much!! This helped immensely! :)
thank you guy, very helpful this video
Yeah! It really blew my mind and a lifesaver on a task I'm working on...
It's the little things 😁👊 Glad it got things sorted for you (see what I did there? 😉)
This was really helpful! Thanks!
really useful tip. how do we make it work when we are displaying data across years?
Thanks so much. I have a question. Can you explain how the same sorting can be done in the DIRECT QUERY OPTION? because the direct query does not have this type of sorting technique. Thanks.
thanks a lot bro... this is extremely helpful..
This was extremely helpful, thank you!
Glad to hear that Juan! 👊
Thanks but how do we solve this problem in a SSAS Direct Connect mode where there is no Visualiser, modeler etc.
Thank you, this was very helpful
I found this very helpful and I'll be using it for other things too. 🎉
Sort by column has been removed from the Modeling Tab (still available in the Data View) in the August 2020 update... DO you know if it has been relocated somewhere else. I'm unable to put my months in order now. My girlfriend has the view on her work computer because they haven't updated versions yet.
This is a good solution within one year. But what is the appropriate solution for time hierarchy based on a specific date (not a month string)?
I want to expand all down one level (e.g. 2018 Q1 to 2020 Q3) and it does not sort properly. Do you have a solution for that?
Thanks! Saved me a lot of headaches! :)
Quick question. I working off of a direct query from a table in SQL and I want to sort by grade (k-12). I created a gradeSort column where k=0 and first = 1 up through 12 grade. When I set the chart to sort by gradeSort it works until I clear my slicer. The chart reverts to sort ascending where grade K is the last grade on the chart. When I go to the table and set the column Grade (in advanced properties) to sort by gradeSort I get a circular reference error. I know I could create the column gradeSort in SQL and it would be included in my direct query, but I am stubbornly trying to solve this in Power BI. Thanks!
unrelated question:
How do you get the dark bg on the right side?
For direct query connections to SQL server, this doesn't seem to work, I have both weekday and weekday # in my SQL but have to resort to having weekday # on the axis of my charts to get the days sorted correctly.
Dude, you saved my life! Thanks a lot!
Thank you! You are hitten gem!
Hi, thanks for solution, however it seems not working when visual contains data longer than 1 year, Any suggestions ?
so easy and sooo useful, thanks!
Very helpful! Tks a lot!
Wow,
I have spent hours with this problem before coming to this.
Thank you very much
Still we are not able to see the modelling option under transfrom data can we you help
Hi Adam, nice video, but the inconvenience is that I can't add a calculated column based on the column I want to sort and then sort the column by the calculated column, which would pop up something saying I can't do that. I usually have to add the order column manually by creating a new two-column order table and import the order column from that table by Power Query, is there a better way to do this?
In this PQ way, I can't sort a column in a table created by DAX, which would not show up in PQ.
Equate the column you want to sort to a calculated column e.g. 'column you want to sort' = 'new calculated column'. Now create an ID/Num column that will sort the original column you want to sort. Now sort the 'New Calculated column' using this new ID. I believe that will work. Give it a try.
Thanks, but I am not sure I understand, in short, the problem is how to create an "ID/Num" column，if you you create it as a calculated column based on the column you want to sort, Power BI will not accept it as a sort standard for there is circular reference or something.
I know about the trick for many Weeks and I just tried it last Week but it did not work do you know why, because I was trying to sort on a column having multiple values for the same month (like a one to one relation between the two columns). So thank you for sharing.
Nice video. Wait can I do the same thing if I want the months to start in July? In my company the fiscal year is from July - June.
This is very good . I'm still looking for the tips to sort the data by financial quarter and month. I also would like to show the data last rolling 12 months. Any help would be appreciated.
Sick content! Thanks!
Thanks, I got the point, you are very very very well, thanks again
Hi Adam, how do you achieve this in AAS?
What if I am trying to sort 40,000+ records? Is there a way of programmatically assigning a number depending on the value?
Love the T-shirt. How did you get the Monthnumber column