Sorting a Quarter year column based on dates PowerBI DAX

[Anonymous]

Hi all! I have the following data table, in which I created a Quater-Year column for 2014 to 2019:

I am creating visualisations with Quater-Year, however the axis are not sorted well (it looks like: Q1 2017, Q1 2018) but instead it should be Q1 2017 Q2 2017 and so on.

If I am trying to sort by another column it says that the levels between the columns must be the same:

However, I have no column with has similar levels to Quater-Year. What can I do to make sure I can sort right on Quater-Year? 

[Anonymous · Accepted solution]

@Anonymous - You can create a new Calculated column that contains a direct match to your Quarter column:

Quarter Key = 'Date'[Year] & 'Date'[Date].[Quarter]

Sort by this new column.

Hope this helps,

Nathan

[Anonymous]

Thank you Natelpeterson!  Can you explain to me why this works? I don't really understand

[Anonymous]

Think about the column and its sorting column in a key-value pair. So, for Q1 2019, you could ask "what is the sorting value?" If there is more than one value it doesn't work. In this case you know that Jan 2019, Feb 2019 and Mar 2019 are all sequential and it should work, but the engine doesn't make any assumptions. Using 2019Q1 works because it satisfies the following requirements:

1. It is a one-for-one match.

2. The alphabetical ordering matches the sequential ordering.

Cheers,

Nathan