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
Anonymous
Not applicable

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

hi, @Anonymous 

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
v-lili6-msft
Community Support
Community 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

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.
Anonymous
Not applicable

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

hi, @Anonymous 

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.
Anonymous
Not applicable

What a silly mistake. Thanks for explaining this, though it took a few knocks on my noggin. It works (for now)! Thanks again for your help Lin. 🙂

 

Robyn

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

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.