Extract month and year from date

[Anonymous]

Hi, 

I'm trying to do a report that tracks completed actions in months over the past 2 years. Therefore I need to extract the month and year from a date into a new column. I have tried the month and year functions but then they are saved as text when I concatenate so can't sort them properly in a graph. 

Any ideas would be helpful.

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous 

Did you use format function to build extract month and year?

If you use format "YYYY" and format "MM", "YYYYMM",your result will show text type.

Power Query:

Select Date column and click Date in Add column.

Then add a custom column and change it type to whole number.

Dax:

In addition to  BA_Pete 's reply, use Year and Month function in calculated column.

If you want to show YearMonth try this dax.

Dax_YearMonth = YEAR('Table'[Date])*100+MONTH('Table'[Date])

Result is as below.

Best Regards,

Rico Zhou

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

[BA_Pete]

Hi @Anonymous ,

For anything date-related, you should set up a separate calendar dimension table and relate this to your fact table.

In DAX, this can be as easy as creating a new table as follows:

dimCalendar = CALENDARAUTO()

Once you have your list of dates, then create new columns with your MONTH() and YEAR() functions from the date.

Concatenate these into another new column which is just the [Year] and [Month] numerical values together, like 202005, 202006 etc. Format this as a number.

Create any other time formats that you want, such as 'monthYearText' (Apr-20 etc.), then select your text formatted version of the date, go to Sort by column on the ribbon, then select your numerical version column to sort the text version by.

Relate your dimCalendar table on [Date] to your fact table data field.

Pete