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

[lsullivan6311]

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?

[sevenhills · Accepted solution]

Dax for the column YYYY-MM:

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

YYYY ~ Number, MM ~ Text 

[v-eqin-msft · Accepted solution]

Hi @lsullivan6311 ,

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.

[sevenhills · Accepted solution]

Dax for the column YYYY-MM:

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

YYYY ~ Number, MM ~ Text 

[lsullivan6311]

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]

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!

[lsullivan6311]

I have gotten a date column, formatted to be YYYY-MM-DD.  I wanted it to be end of month, but since I only had Year column and Month number column, I ended up using day 15 (I definitely did not want beginning of month).  I changed the data type to be Date and I chose YYYY-MM-DD, but Power BI now shows the date in this column as (example - Friday, February 15, 2015)  I do not want the day of the week.  Changing the format does not work.

[lsullivan6311]

That worked.  Thank you!