Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sorting a Quarter year column based on dates PowerBI DAX

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? 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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