Power BI Tips - How to sort by Month and Year (and best practices)
Vložit
- čas přidán 22. 12. 2021
- Sorting data in Power BI is very common. In this video, we go a little bit deeper and take a look at how to sort a column that has two attributes. We will look at two different approaches and then explain why one is considered a best practice. Enjoy!
If you enjoy this video and are interested in formal training on DAX, Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Mitchell20" to receive a discount at check out when purchasing On-Demand Learning classes from pragmaticworks.com/pricing/
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning - Start With The FREE Community Plan: tinyurl.com/2ujujsb5
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/
Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️CZcams: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: (904) 638-5743
Email: training@pragmaticworks.com
~-~~-~~~-~~-~
Please watch: "(275) Free Pragmatic Works Power Apps Component Library "
• Free Power Apps Compon...
~-~~-~~~-~~-~
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.
Well Done mate! I have been looking for so many videos for this problem and finally i have find it. Thank You!
Finally one version that is working 😀, you very skilled and explain things perfectly, thank you very much
Thank you for the detailed tutorial, it's working great :)
I freaking love this guy man! He always solves my problems.
Very helpful, thank you!
Thank you, I cracked my head trying to figure this out.
Thanks for this useful video, really helped me a lot.
Wicked helpful. Thanks so much!
literally crying with gratitude
Glad you enjoyed!
You Guys are Just amazing (All Pragmatic Works Stuffs)
This had been driving me round the bend. Thank you
Thanks for the explanation Mitchell
clear and concise. worked perfectly, thank you!
Glad to be of help!
Thanks man it worked
Great, thank you
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
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?
thanks, worked perfectly!
Glad to hear you found it helpful :)
Nice trick. Thank you
You're welcome!
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.
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.
Informative Sir
Thanks! -Mitchell Pearson
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?
what if I donot have date column? I only have Year and Month Column?
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.
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.
I do it a bit simpler by calculation.... YYYY * 100 + DD...
sort by month year order column :) YYYYMM
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.
As always, Thank you! -Mitchell Pearson
I let your pain through me
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