Forum Discussion
Sorting dates into quarters from different years
Hello I am having issues sorting my data from different quarters in a chronological order.
For example I want to sort data from Q3-2018 to Q1-2019. So the data should line up Q3-18, Q4-18, Q1-19, but it is not (see picture). It is also consolidating Q4 data from both 2018 and 2019 into the same Q4 column.
Also, I have verified that the data is being recognized as Date/Time in the table.
Any help on this? Thanks
hi, Anonymous
Please refer to this screenshot:
You have to keep Year hierarchy in the visual too, otherwise, it will be aggregated in quarter hierarchy.
"however, drill down does not fix my problem.", Do not use drill down, you should use expand.
and here is a simple sample pbix file, please try it.
Best Regards,
Lin
10 Replies
Hi,
My suggestion would be to always use a date table. It's a table you save somewhere on your desktop and always use in every report. In the table, you'll have each quarter mapped to a year and Power BI will recognise it as a hierarchy, so when you use it as a field you can put year and underneath quarter, which will solve your issue.Here's how it looks for me in a simple visual:
I can choose to stay at fiscal year, or go down to quarter or month.
You can easily download a ready made date table from many sources, here's one: https://www.sqlbi.com/tools/dax-date-template/
Hope this helps!
Eva
Hi,
The date table stays forever. It just has every date you could ever need (mine runs from 2005 to 2025) and maps it to a month, a quarter, a year, whatever you need.You just import it every time you create a Power BI report which uses dates, and create a relationship between the date table and the column in your database that has your dates.
Eva
v-lili6-msftCommunity Support
hi, Anonymous
For your case, you just need to learn the difference Drill pathways in drill model.
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill#drill-pathways
From your screenshot, we could know that you used drill down for the date column, that will only get the next level data.
For example, when you drill down on Year level, you will only get quarter level.
So different year value will be aggregated for the same quarter.
You should use Expand in drill model
For example, when you expand on Year level, you will get Year-Quertar, and it will sort by correct way.
Regards,
Lin
Hello Lin,
Thanks for your suggestion, however, drill down does not fix my problem. I do have drill down enabled and even with the date hierarchy, PBI is not recognizing the year difference among the quarters. Like I mentioned above, my data timestamps are from Q3-2018 to Q1-2019, but PBI will only sort the quarter numerically instead of chronologically 4-3-1 or 1-3-4(see first picture). I need it to recognize that Q3 and Q4 are of the previous year and Q1 of the later year.
Robyn
- v-lili6-msft
hi, Anonymous
Please refer to this screenshot:
You have to keep Year hierarchy in the visual too, otherwise, it will be aggregated in quarter hierarchy.
"however, drill down does not fix my problem.", Do not use drill down, you should use expand.
and here is a simple sample pbix file, please try it.
Best Regards,
Lin