Remove duplicates and count from date column in new table

[Jay2022]

I have selected a dates column from another table and i now have them in a new column  

Select Col =
SELECTCOLUMNS('Table')
,"Dates", 'Table'[Dates column])

I have then changed the format of the dates to MMM,Year 

so i have

Jan24 

Feb 24

Feb 24 

Mar 24 

etc 

I now want to 

1 - remove duplicates

2- count the number of occurances for each month eg 

Jan 24 - 5 

Feb 24 - 2 etc 

Do I do as above and count the occurances from the Dates Column from th eoriginal table or do i add another column to this table the same as Dates only without the duplciates and then count from the table containing the duplicates. 

so it woudl look like 

Dates, Date_excluding_duplicates, Count

Any help really apprecaited

[v-tangjie-msft · Accepted solution]

Hi @Jay2022 ,

Thanks bhanu_gautam  for the quick reply. I have some other thoughts to add:

(1) We can create a new table.

Col = 
SELECTCOLUMNS('Table'
,"Dates", 'Table'[Date column])

(2) We can create columns on Col table.

MMM_Year = FORMAT([Dates],"MMM yy")

Count = COUNTROWS(FILTER('Col',[MMM_Year]=EARLIER('Col'[MMM_Year])))

(3) We can create a SUMMARIZE table.

SUMMARIZE Table = SUMMARIZE('Col',[MMM_Year],[Count])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Jay2022]

are these each 3 seperate tables ? 

[Jay2022]

i get the following  error     The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Jay2022]

Thanks first bit works fine but getting stuck on this bit 

Format the dates to "MMM, Year":
FormattedDatesWithCount =
ADDCOLUMNS(
UniqueDatesWithCount,
"FormattedDate", FORMAT('Table'[Dates column], "MMM yy")
)

after FORMAT shoudl it be (UniqueDateswithCount'[Dates colum] basically from the new column i created

[bhanu_gautam]

@Jay2022 , Use below steps

Create a new table with unique dates and their counts:
UniqueDatesWithCount =
SUMMARIZE(
'Table',
'Table'[Dates column],
"Count", COUNT('Table'[Dates column])
)

Format the dates to "MMM, Year":
FormattedDatesWithCount =
ADDCOLUMNS(
UniqueDatesWithCount,
"FormattedDate", FORMAT('Table'[Dates column], "MMM yy")
)

Remove duplicates and count occurrences:
FinalTable =
SUMMARIZE(
FormattedDatesWithCount,
[FormattedDate],
"Count", SUM([Count])
)
This will give you a table with the formatted dates and their counts without duplicates.