Forum Discussion
Extract month and year from date
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.
Super UserHi 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