cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kenjoswe
New Member

Distinct months..?

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.

1 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!

73EF7E92-0B12-4A52-92DD-95D0FD56CA84.png

View solution in original post

7 REPLIES 7
tomfox
Super User
Super User

Hi @Kenjoswe ,

 

How about creating a custom column like this:

tomfox_0-1648411335223.png

 

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:

tomfox_1-1648411428893.png

 

 

Or you create a separate custom table like this:

tomfox_2-1648411532323.png

 

Let me know, if this helps!

 

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

 

Thanks!

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

Kenjoswe_0-1648453526111.png

 

Hi @Kenjoswe ,

 

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

 

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

OK, I still have error on 'ProductionDate'

Kenjoswe_0-1648455277767.png

 

Hi @Kenjoswe ,

 

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/

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

73EF7E92-0B12-4A52-92DD-95D0FD56CA84.png

tamerj1
Super User
Super User

Hi @Kenjoswe 

You may try

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors