Power BI Tips - How to sort by Month and Year (and best practices)

[@truewiking]

Finally one version that is working 😀, you very skilled and explain things perfectly, thank you very much

[@war_kittens]

THANK YOU. I have seen so much absolute BS on how to make a sort column and I KNEW it had to be easier than people were making it out to be. There was NO way that you couldn't use a data transform. And the new column by example? Holy crap do I have ideas for that thing. You have absolutely saved my bacon.

[@advanttb8991]

literally crying with gratitude

[@PragmaticWorks]

Glad you enjoyed!

[@gentirexhaj5876]

Well Done mate! I have been looking for so many videos for this problem and finally i have find it. Thank You!

[@duduzilembhele8576]

Thank you, I cracked my head trying to figure this out.

[@AlastairDonnelly]

This had been driving me round the bend. Thank you

[@HakeemLawrence]

I freaking love this guy man! He always solves my problems.

[@kokilad-vq5je]

Thanks for this useful video, really helped me a lot.

[@shairadoshitzky1077]

Thank you for the detailed tutorial, it's working great :)

[@ershelin]

Thanks for the explanation Mitchell

[@monopolymac6168]

clear and concise. worked perfectly, thank you!

[@PragmaticWorks]

Glad to be of help!

[@haymanotachn2234]

You Guys are Just amazing (All Pragmatic Works Stuffs)

[@naoletube6017]

Thank you very help full

[@ContemplationsYT]

Very helpful, thank you!

[@brendanobrien2253]

Wicked helpful. Thanks so much!

[@Lhen134]

thanks, worked perfectly!

[@PragmaticWorks]

Glad to hear you found it helpful :)

[@ravitejak9414]

Thanks man it worked

[@mohanadmohamed5501]

Nice trick. Thank you

[@PragmaticWorks]

You're welcome!

[@rahimovassagh4569]

Great, thank you

[@danielgelderd4017]

Thanks. Newbee question. Why could you not just select the existing date field as the thing you sort using?

[@williamarthur4801]

Well thanks for one thing, I'd been struggling with a sort column YEAR()&MONTH(), which was fine up to September, so that's now sorted, no pun intended.

[@CSHarvey]

MonthID = Datediff("month",#1/1/1900#, [Date]). This gives a 1-up index for each month that can be easily used for sorting by year/month, Filtering for the past x months (where MonthID > DateDiff("Month",#1/1/1900#,Today)-(x+1)) comparing months YoY (recursive join on the data "On A.MonthID = B.MonthID +12") Same process works for ISO Weeks.

[@zxu-zv2yy]

Hi Michell, this is great! Would you please let me know if the query is till the better option when we combine the multiple period file at a time by using the query method to sort the MonthYear out and even later there will be new period file added to? Thank you

[@carolwu7270]

Thank you so much, it is helping me solving the ordering problem. However, I use the month-year in my Matrix's as column, is there anyway I can sort the month-year in descending order?

[@rahmasye001]

Informative Sir

[@PragmaticWorks]

Thanks! -Mitchell Pearson

[@andrewyoung9180]

How did you go from creating the Month Year Year Sort to then getting the two lines below it? You clicked something and did not say what you clicked.

[@lucky12303]

Thank you, from past one week I am struggling to sort the my data but unfortunately its not working. I have 3 years data with a) Month Numbers b)Year (But need to arrange as per fiscal year Apr to Mar) Want to sort the data as per fiscal year but its not working, could you please help me?

[@varshakrishna7918]

what if I donot have date column? I only have Year and Month Column?

[@LuisECP]

What is Calendar Year? is the numerber of year, maybe you must show the table first, so we can give us a better idea how it work.

[@adityagedam4670]

i get error of circular dependency

[@xMastJedi]

I do it a bit simpler by calculation.... YYYY * 100 + DD...

[@Chris_87BC]

sort by month year order column :) YYYYMM

[@xMastJedi]

Brrrr, Dates in Power BI and reports it's something what I hate... And, sorry guys, american date and time format I hate most :P Why? I can show you excel file with data taken from system in csv format, where in ONE column I have mix format - EU dates, US dates, 24 hrs clock and 12 hrs clock.... And obviously half of them are recognised as text. And you never sure are these transformed to date are transformed correctly. And 05/07/2021 is not regonised as 7th May... NIGHTMARE. Or you living in Europe, you create excel table to collect data, you save it on SharePoint Teams, you open it and you can see.... US format dates looool. Easy to imagine - human error by typing wrong date. PS1. All International seetings - system, browser, office I have set on UK format... DD/MM/YYYY 24 hrs clock. PS2. Next nice trick. Thx.

[@PragmaticWorks]

As always, Thank you! -Mitchell Pearson

[@advanttb8991]

I let your pain through me

[@aviellsegall1190]

Power BI is business Intelligence. It uses AI Artificial Intelligence. So why on earth it does not have DI - Date Intelligence ??!!. When column set as "Date", Power BI should configure the month's number AUTOMATICALLY !!!. Sincerely yours, AIU...Average Intelligent User