Help with Custom Date Column and Sorting

[JMAlloway]

Hello All -

I am looking to figure out how to sort a custom date column. The date column takes data from January 1st 2018 - current and assigns the relative quarter and year. What's different is that the custom date column deviates from quarters to months, if the month is within the current year. The DAX command below is what I am using to achieve this result:

DateAxisValue = 
VAR CurrentYear = YEAR(TODAY())
Return
IF (
('Calendar Table'[YEAR]) = CurrentYear, 'Calendar Table'[MONTH], "Q" & ('Calendar Table'[QUARTER] & 'Calendar Table'[YEAR])
)

I'm at a loss in figuring out how to sort this column properly. I am trying to sort in calender order; meaning Q1 2018, Q2 2018, Q3 2018, ....... Q3 2019, Q4 2019, January, February, March, April, May, June.

I know the answer is probably simple, but I've looked at this problem for way too long. So now I am asking for help. 

Thanks for any input!

[nandic]

@JMAlloway , in this case you need to compare date to current year.

Steps:
1) You need column in format Q1 2016 and column in format Year * 100 + Quarter (202001). You will sort first column by second column.
Dax example: 

Month Number = MONTH('Date'[Date])

Year Number = YEAR('Date'[Date])

Month Name = FORMAT('Date'[Date],"mmm")

Month Year = 'Date'[Month Name] & " " & 'Date'[Year]

Quarter Year = "Q" & 'Date'[Quarter] & " " & 'Date'[Year]

Quarter Year Sort = 'Date'[Year] * 100 + 'Date'[Quarter]

Month Year Sort = 'Date'[Year] * 100 + 'Date'[Month]

2) You need column in format Jan 2016 and column in format Year * 100 + Month (202001). You will sort first by second column.
3) After that create "Dynamic column" which will compare year versus current year. If year < current year then return "Quarter Year"
else return "Month Year".
Here is formula in Dax: 

Dynamic Period = IF(YEAR('Date'[Date])<YEAR(TODAY()),'Date'[Quarter Year],'Date'[Month Year])
4) Finally add column which will sort column in step 3)

Dynamic Period Sort = IF(YEAR('Date'[Date])<YEAR(TODAY()),'Date'[Quarter Year Sort],'Date'[Month Year Sort]*10)
-- explanation: 'Date'[Month Year Sort]*10: we multiply it to get higher value than 'Date'[Quarter Year Sort], because we want to see months after quarters on chart.

When you set this column in the chart it should look like this:

 Date Table:

Cheers,
Nemanja

[nandic]

Hi, There are several solutions, but they all have one main thing - you need column which you will use for sorting.

Example: if you display date in this format "Q1 2019" you need column in format 201901; "Q2 2019" will have value 201902.

So when you choose option to sort column "Quarter Year" by this new column "Quarter Year Sort" it will be sorted the right way.

Example of adding sorting column:

Power Query: [Year]*100+Date.QuarterOfYear([Date] -- if you have column for year, but missing column for quarter, so you get it based on date column

Dax: YearMonthSort = YEAR('Date'[Date]) * 100 + QUARTER('Date'[Date]) -- if you have no column for year and quarter so get it from date column

Final step to set sorting:

[JMAlloway]

Thanks for the reply, @nandic. I think this method would work under normal circumstances. My issue is that I have "Q2 2019" values and values that are "June 2020". So I have quarters and months that needs sorted in calendar year order. I'm having difficulting writing something similar to your example, although it has been the closest to working. I provided an image below that shows the result of your DAX example. It works for the quarters, but does not for months.

[AntrikshSharma]

you can create a column that lists out [Month Number] and based on this column you can sort your [Month Name] column by using the sort by column feature in the modelling tab.

[JMAlloway]

Thanks for your suggestion, Antriksh. Unfortunatly, the data goes back to January 1st 2018. If I try to do this, I recieve a cardinality error. 

Here is how the visual shows up now:

In need this sorted in calendar order.

Thanks.