Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Simple issue but devilish: I have multiple year data with a simple 'create date' field in it for each record.
For one view I'd like to show the data in chronological order in a simple stacked chart, but when i span multiple years it shows months in year order (Jan - Dec) and just puts the data from each month side by side
Ie. if i show July 18 - July 19 it starts with Jan 19, Feb 19....July 18/July 19, Aug18/Aug 19, etc.....
I would like to show earliest date to current date (true chronological order)
July 18, Aug 18, Sept 18, ..... Jan 19, Feb 19, ,....July 19
so the above chart would START with July 18 (orange) and proceed through chronologically to July 19 (DkBlue)
Any ideas or visuals that would assist?
Solved! Go to Solution.
Hi @Anonymous
Please watch the video in the link below.
https://www.youtube.com/watch?v=NlX7Cp2P0d4
@Anonymous if you want to use power query to add columns, you following syntax
Month Year = Date.ToText([Date],"MMM-yy")) Month Year Sort = Date.ToText([Date],"yyyy-MM"))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@AliceW Glad, it helped and worked out for you. Cheers!!
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Creating the two new fields in PQ as such
and then selecting the field and Modeling and sorting by the new sort field worked.
Thanks!
@Anonymous you need to add columns in your table to achieve this
Month Year = FORMAT( Table[Date], "MMM-YY" ) Month Year Sort = FORMAT( Table[Date], "YYYY-MM" )
Selected your Month Year column, go to modelling tab and select sort by option from menu and choose Month Year Sort
After all above done, use Month Year on x-axis and year on legend and you will get the result.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Parry, I'm a bit late with this reply, but it was a life-saver! Thanks, man.
To add column, like so?
o?
when i try it get error
@Anonymous sorry that was DAX expression not power query.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous if you want to use power query to add columns, you following syntax
Month Year = Date.ToText([Date],"MMM-yy")) Month Year Sort = Date.ToText([Date],"yyyy-MM"))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi there!
In the reports I've been working on, I have come upon this issue, and I think my solution is pretty reliable.
I used a DAX formula to generate a custom column that concatenates the year and the month into a number as follows:
usageDate = Table[Date].[Year] & FORMAT(Data[Date].[Month], "00")
So now usage date looks something like "201904" or "201912". From there, you may need to create another custom column that has both the month name and the year in it. I don't think you will be able to just sort a column of months with my following method since there will be repeats in the month column otherwise.
Go to modeling, click Sort by Column, and select the new usageDate column. As a result, the Month-Year column (which you will have to create a custom column for) will now sort by the usageDate which is effectively just a number that increases as months and years go on.
Hope this helps!
Thank you @Anonymous , I think this'd work for me too!
I've made my own solution, but came to look for other solutions, since I was pretty sure it's not the optimal one. I made a copy of the year column, squared it and then made a new column with year(square)+monthnumber. So for example the values for Oct 2018 to Mar 2019 are
Oct 2018: 4 072 334
Nov 2018: 4 072335
Dec 2018: 4 072 336
Jan 2019: 4 076 362
Jan 2019: 4 076 363
Jan 2019: 4 076 364
After this I can use this new column to rank the order of the months, even if there's information divided montly from multiple years. It works, but is a bit crude hack.
Hi @Anonymous
Please watch the video in the link below.
https://www.youtube.com/watch?v=NlX7Cp2P0d4
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |