Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
I created a copy of the column ProductionDate with the format 'mmmyyyy'. Then I grouped by the copy (MonthYear) and summarized 'ItemCount'. Success!
Hi @Kenjoswe ,
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thanks!
This is what I got, my field ProductionDate is not recognized:
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! |
#proudtobeasuperuser |
OK, I still have error on 'ProductionDate'
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! |
#proudtobeasuperuser |
I created a copy of the column ProductionDate with the format 'mmmyyyy'. Then I grouped by the copy (MonthYear) and summarized 'ItemCount'. Success!
Hi @Kenjoswe
You may try
Month =
VAR CurrDate =
SELECTEDVALUE ( Table[Date] )
RETURN
FORMAT ( DATE ( 1, MONTH ( CurrDate ), 1 ), "mmm" )
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |