Convert Year and Quarter into YYQQ using DAX

[Anonymous]

Hello, @Anonymous 

I have dataset that contains date and have to manually create the formula in excel to extract the Year and Quarter from the date and then upload it into Power BI. But, this is a very time consuming step as its required every time the data is downloaded.

Is there a way to convert it into the Power BI directly using DAX such that the formula will stay and when the file is refreshed it would automatically calculate the extracted Year and Quarter as YYQQ in a column?

Appreciate any help with this please.

Thanks

[Anonymous]

Thanks @Max_  , it's working fine now. I was able to get the date extract into this format. 

[Max_]

Hi @Anonymous ,

Have you tried creating a new calculated column and assemble the columns using the FORMAT function?

Date (YYQQ) = FORMAT([Date], "YY") & "Q" & FORMAT([Date], "q")

 
As an example, the date 31/01/2024 would be converted into 24Q1. Is this what you are looking for? 

[Anonymous]

Hi - Yes, but Power BI query gives me an error that the function FORMAT is not recognized. Also, it does not work as the Measure. Please could you check if this works at your end or I am missing something?

[Max_ · Accepted solution]

Hi @Anonymous ,

Yes, I quickly tested this in one of my reports that contains a date column. Here are the steps that I did:

1. Go to table view of the report. The column I used is called "Period" and formatted as a Date value. 
   
2. Add a calculated column:
   
3. Enter the code from my previous answer (might be required to change the column name of the Date column, e.g. mine is called Period):
   
4. New columns gets calculated:
   
   
   

Did you do it the same way? At which point did you experience the error (and do you have some screenshots that would help me to better look into this)?