cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbirch4404 Frequent Visitor
Frequent Visitor

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. 

 

Date Format Incorrect PBI.PNG

Also, I have verified that the data is being recognized as Date/Time in the table.

 Date Format Incorrect PBI_2.PNG

 

Any help on this? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sorting dates into quarters from different years

hi, @rbirch4404 

Please refer to this screenshot:

1.JPG

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

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

10 REPLIES 10
evammun Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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:

Capture.PNGI 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

rbirch4404 Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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

evammun Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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

rbirch4404 Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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

evammun Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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

rbirch4404 Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

Hello again Eva,

 

I've attached a screenshot of the list that populates from my "Get Data" tab. I do not see Power BI database - ony datasets and dataflows. 

 

Power BI database.PNG

 

I tried to connect to datasets but it says I cannot connect to it and another data source at the same time. Can you clarify? Thanks. 

 

Robyn

 

Community Support Team
Community Support Team

Re: Sorting dates into quarters from different years

hi, @rbirch4404 

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

8.JPG

For example,  when you expand on Year level, you will get Year-Quertar, and it will sort by correct way.

 

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.
rbirch4404 Frequent Visitor
Frequent Visitor

Re: Sorting dates into quarters from different years

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

Community Support Team
Community Support Team

Re: Sorting dates into quarters from different years

hi, @rbirch4404 

Please refer to this screenshot:

1.JPG

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

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors