Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a Date column or measure with two existing columns, Month (Text) and Year (Text) formats

I need a date column for Chart visualizations to show month/year, current year vs prior year, but my columns are month (which is text and the month number, 1,2,3, etc.) and year (which is text).  How can I combine these into one column with YYYY-MM format?

Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago

    Dax for the column YYYY-MM:

     

    YYYY-MM = 'Table'[YYYY] & "-" & Right("00" & 'Table'[MM], 2)

     

     

    YYYY ~ Number, MM ~ Text 

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think sevenhills 's method is the most convenient and it has solved your issue,so please kindly Accept his reply as the solution. More people will benefit from it. 

     

    You could also try:

    YYYY-MM = FORMAT(CONVERT(COMBINEVALUES(" ",[Year],[Month Number],"1") ,DATETIME),"YYYY-MM")

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    4 years ago

    Dax for the column YYYY-MM:

     

    YYYY-MM = 'Table'[YYYY] & "-" & Right("00" & 'Table'[MM], 2)

     

     

    YYYY ~ Number, MM ~ Text 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That worked.  Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This gave me a column that looked like a date, but it does not seem to work as a date, since I cannot create a relationship between the date table and this new column.  I need to do this in order to get some calculations.

      • sevenhills's avatar
        sevenhills
        Icon for Super User rankSuper User
        4 years ago

        If you want it as date, then you need to adjust the Data Type and Format. Let us look again, I am providing three ways, you can decide any ONE 

         

        a) You can create and use DAX columns as like below. Any one is good enough

        YYYY-MM = 'Table'[YYYY] & "-" & Right("00" & 'Table'[MM], 2)

         

        YYYY-MM2 = 'Table'[YYYY] & "-" & Right("00" & 'Table'[MM], 2) & "-01"

         

        YYYY-MM-DD = Date('Table'[YYYY], 'Table'[MM], "01")

         

        b) all newly created columns, select the data type as "Date"

         

         

        Once you select the data type as date, you can choose the format of your choice.

        - All three columns created are "date" data type

        - format only varies

         

        Hope this helps!

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think sevenhills 's method is the most convenient and it has solved your issue,so please kindly Accept his reply as the solution. More people will benefit from it. 

     

    You could also try:

    YYYY-MM = FORMAT(CONVERT(COMBINEVALUES(" ",[Year],[Month Number],"1") ,DATETIME),"YYYY-MM")

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.