Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duncan3172
Regular Visitor

Sorting X axis values

Im a realitively new PBI user and now that we have rolled over into 2020 im having an issue with my date values on the X axis of a line plot.

 

My plot is displaying the data for Q1 of this year before Q2, Q3, and Q4 of last year and it continues when drilled down showing January of this year followed by November then December of last year

 

dates are being brought in via direct query in a DD/MM/YYYY format.

 

how can i resolve this and return everything to chronological order

1 ACCEPTED SOLUTION

HI @duncan3172 

You could just use Sort by column in Power BI to get it.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

https://radacad.com/sort-by-column-in-power-bi

Just add a year-quarter column in edit queries or data source

3.JPG

Then set sort by column for quarter column

 

and you could use the same logic for month column (Add  a year-month column that [Year]*100+[Month])

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8

Hi @duncan3172 ,

 

do you have a date table?
The year must also be displayed for correct display.
e.g.
2019 Q2.
2019 Q3.
2019 Q4.
2020 Q1.

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Greg_Deckler
Super User
Super User

Use the forked arrows drilldown icon instead of the parallel arrows drilldown icon.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

When using the forked arrow for drilldown it skips month and only shows days where data was actually collected. using the paralell arrows is the only way to get it to drill through the date hierarchy correctly. year-quarter-month-day. it displays the yearly data correctly 2019 then 2020 but as soon as you drop to quarter it sorts numericaly 1-2-3-4 when it should be 2-3-4-1 to keep the timeline in order, the same goes form months, its sorting jan-nov-dec instead of nov-dec-january. its like it forgets the year exists as soon as it drills below that level.

Yes, the year information is no longer available at that hierarchy level.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


mwegner, so if i want it to work correctly i just have to tell it to filter out last year's data? simple enough

 

 

Hi @duncan3172 ,

 

you can filter out the last year or you need a "Label" with a Year information.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


HI @duncan3172 

You could just use Sort by column in Power BI to get it.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

https://radacad.com/sort-by-column-in-power-bi

Just add a year-quarter column in edit queries or data source

3.JPG

Then set sort by column for quarter column

 

and you could use the same logic for month column (Add  a year-month column that [Year]*100+[Month])

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You could try creating a sort by column that includes the year and then the quarter name. You also might changing your axis from categorical to continuous or vice versa perhaps. Hard to replicate what you are seeing without sample data. If a quarter or month has no data it won't display but often there are settings for this to include items with no data.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.