Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kenjoswe
Regular Visitor

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
tackytechtom
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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors