Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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.
Use the forked arrows drilldown icon instead of the parallel arrows drilldown icon.
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.
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 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
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |