Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Convert Year and Quarter into YYQQ using DAX

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

Need Help
  • Max_'s avatar
    Max_
    1 year ago

    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)?

4 Replies

  • Max_'s avatar
    Max_
    Icon for Helper I rankHelper I
    1 year ago

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

      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_'s avatar
        Max_
        Icon for Helper I rankHelper I
        1 year ago

        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)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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