Need help with DAX expression to create a Quarter and Year calculated column

[clp7285]

I've already created a "Quarter" calculated column to display Q1, Q2, Q3, Q4 by doing some research online. Quarter = FORMAT('Date'[Date],"\Qq")

 

I have a 'Date" table and 'Date' field, and also a 'Year' field. 

Date =

ADDCOLUMNS (FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= 2019, YEAR ( [Date] ) <= TODAY() )

),

"Year", YEAR ( [Date] ),

For a matrix header I need to have the headers as Q1 2020, Q2 2020, Q3 2020, Q4 2020, Q1 2021, Q2 2021, Q3 2021, Q4 2021, Q1 2022, Q1 2022. I'm trying to create a calculated column to combine both "\Qq" space then "YYYY" but I can't seem to get it to work. Would I need to use a different DAX function than FORMAT? I'm having a hard time figuring out the correct DAX syntax. Any help would be appreciated.

Christine

[Whitewater100]

Sorry:

Try this..

Q = "Q" & FORMAT(DATES[Date],"Q YYYY")

This will work and show Q1 2022   etc...

[clp7285]

Whitewater100,

I couldn't get DATES to select so I needed to alter it a bit to 'Date' instead of DATES but it worked! Thanks!

Quarter and Year = "Q" & FORMAT('Date'[Date], "Q YYYY").  

The only problem is that it is displaying Q1 2020, then Q1 2021, then Q1 2022. It puts all the Q1's together then the Q2's etc. I want it to be chronological Q1 2020, Q2 2020 so sorted by year and then Q1, Q2, etc. - all the 2020 quarters show before moving into 2021.  When I click on the thee horizontal dots 'Sort by" Year is not listed as an option. Not sure how to get this to be in the order I need. Any suggestions?

[Whitewater100]

Hi:

Yes, your Date table is named Date and mine is Dates.

To answer your question.

Please have another CC (this will be to sort y)

QuarterSort = FORMAT(DATE[Date], "YYYYQ")

Now click on your Q1 2022 column (the one we did earlier) and choose "Sort by Column" at top. Now choose this new column to sort the first one.

If fine, can you mark as accepted solution? Thanks..

Here's a image to help demo.

[clp7285 · Accepted solution]

Thank you. The QuarterSort, Sort by Column worked.

[Whitewater100]

Hi:

That's great! Can you mark my answer as accepted solution? Have a nice day. 

[Whitewater100]

Hi Christine:

You can add a CC to your dates table

Q# 

= "Q" & FORMAT(DATES[Date],"Q")

I hope this helps!