Forum Discussion
Sorting dates into quarters from different years
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
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
Hello Eva. Thanks for the suggestion.
Would this table only apply to once off data? This data will be streaming from an Azure Server, so I cannot simply copy information into a nicely structured table.
Robyn
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
Hello Eva,
I apologize as I am new to DAX tables. Do I import this file as a new visualization? When I try to import, PBI looks for visual files (*.pbiviz) however, this file downloads as a .pbit and .pbix. I'm a little confused how to import this into my existing .pbix visual dashboard. If you could break it down into steps, I'd appreciate it. Thanks!
Robyn
Hi!
You're just importing it as a data source, not as a visual (in 'Get Data'- Power BI database). Think of it as another part of your data- it's just a mapping table.
Then, to create the relationship, have a look at: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
Best,
Eva