Distinct months..?

[Anonymous]

Hi,

I have dates in a table with the format yyyymmdd.

I want to populate a list in a report with distinct months with the format mmm

How can I do this?

Kent J.

[tackytechtom]

Hi @Anonymous ,

How about creating a custom column like this:

Here the code:

CustomColumn = 
FORMAT (
    DATE ( 
        LEFT ( [date], 4 ),
        RIGHT ( LEFT ( [date] ,6 ), 2 ),
        RIGHT ( [date], 2 )
    ), 
    "mmm"
)

After that you can either use that column in a report:

Or you create a separate custom table like this:

Let me know, if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Thanks!

This is what I got, my field ProductionDate is not recognized:

[tackytechtom]

Hi @Anonymous ,

I think you need to specify the table before the column.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

OK, I still have error on 'ProductionDate'

[tackytechtom]

Hi @Anonymous ,

Are you using a measure instead of a column maybe? 

You may share some data if you like so I can look into it, too!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous · Accepted solution]

I created a copy of the column ProductionDate with the format 'mmmyyyy'. Then I grouped by the copy (MonthYear) and summarized 'ItemCount'. Success!

[tamerj1]

Hi @Anonymous 

You may try

Month =
VAR CurrDate =
    SELECTEDVALUE ( Table[Date] )
RETURN
    FORMAT ( DATE ( 1, MONTH ( CurrDate ), 1 ), "mmm" )