topic Re: Distinct months..? in DAX Commands and Tips

Distinct months..?

Anonymous — Sun, 27 Mar 2022 17:48:17 GMT

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.

Re: Distinct months..?

tamerj1 — Sun, 27 Mar 2022 18:22:22 GMT

Hi Anonymous

You may try

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

Re: Distinct months..?

tackytechtom — Sun, 27 Mar 2022 20:07:27 GMT

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/

Re: Distinct months..?

Anonymous — Mon, 28 Mar 2022 07:47:54 GMT

Thanks!

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

Re: Distinct months..?

tackytechtom — Mon, 28 Mar 2022 07:51:27 GMT

Hi Anonymous ,

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

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

Re: Distinct months..?

Anonymous — Mon, 28 Mar 2022 08:18:52 GMT

OK, I still have error on 'ProductionDate'

Re: Distinct months..?

tackytechtom — Mon, 28 Mar 2022 19:12:44 GMT

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/

Re: Distinct months..?

Anonymous — Mon, 28 Mar 2022 19:32:46 GMT

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