Forum Discussion
Creating a Date column or measure with two existing columns, Month (Text) and Year (Text) formats
Dax for the column YYYY-MM:
YYYY-MM = 'Table'[YYYY] & "-" & Right("00" & 'Table'[MM], 2)YYYY ~ Number, MM ~ Text
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.
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.
Super UserIf 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!
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.