Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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?

2 ACCEPTED SOLUTIONS
sevenhills
Super User
Super User

Dax for the column YYYY-MM:

 

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

 

 

YYYY ~ Number, MM ~ Text 

sevenhills_0-1648490381266.png

 

View solution in original post

Anonymous
Not applicable

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

Eyelyn9_0-1648696621595.png

 

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Eyelyn9_0-1648696621595.png

 

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
Super User
Super User

Dax for the column YYYY-MM:

 

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

 

 

YYYY ~ Number, MM ~ Text 

sevenhills_0-1648490381266.png

 

Anonymous
Not applicable

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.

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"

 

sevenhills_0-1649089090085.png

 

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

sevenhills_1-1649089207134.png

 

Hope this helps!

 

 

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

That worked.  Thank you!

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors