Convert date to Month & Year to use af filter

[Anonymous]

Hi, 

I've made a calculated column in my Customer table that calculates each customers first day of purchase.

I now want to convert that date into a month and year filter, so I for example can filter out customers that was new in August 2016.

My first method was to take the calculated colum and then create a secund calculated column with "First Purchase Month = FORMAT(Customer[First Purchase Date];"MMMM YYYY"), which works, but returns a text column that can't be sorted based on dates - Not ideal.

Secondly I tried creating a calculated column and then simply change the formating of the column to "mmmm yyyy" in the Modeling tab. But when taking that calculated column into a filter, PowerBI will show the correct text formating, but will show a filter value for each date in the column.

How do I create a filter based on the calculated date that I can use as a filter, only showing the distinct months and years?

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

My first method was to take the calculated colum and then create a secund calculated column with "First Purchase Month = FORMAT(Customer[First Purchase Date];"MMMM YYYY"), which works, but returns a text column that can't be sorted based on dates - Not ideal.

---

To sort the "First Purchase Month" column based on dates, you can use the formula below to create a new calculate column called "First Purchase Month Id" first, and then use Sort by Column option under Modeling tab to sort "First Purchase Month" column by "First Purchase Month Id" column. 

First Purchase Month Id =
YEAR ( Customer[First Purchase Date] ) * 100
    + MONTH ( Customer[First Purchase Date] )

Regards