the calendar data list show duplicate date

[Anonymous]

Hello Community, 

I created a calendar using 

Dates = CALENDARAUTO()

Can anyone explain why it shows so many duplicate date for one single month ? how to fix this. thank you so much.

[edhans]

That is just the format. There are going to be 31 of those for January 2014, one for each day of the month.

I would strongly discourage the use of CALENDARAUTO(). It will create a calendar based on ALL date fields in the model, whether they are relevent or not. I've seen models balloon because of this. If there are birthdays in it, you get data back to the early 1900's, or if there are promotions with no true expiration date, you'll see dates through 2099, or even 2999 depending on how users enter data.

Check out this example of how to do a dynamic table that you fully control, yet will have all of the relevant dates in your date table. Creating a Dynamic Date Table in Power Query

[Anonymous]

@edhans thank you, I will defintely try the new way next time.

Is there anyway to fix what I have so far. want to understand why it happen like that. thanks

[amitchandak]

@Anonymous , I doubt it duplicates. Can change the format of date and check. Screenshot not showing day

[Anonymous]

@amitchandak You are right, it is not exactly duplicate the date. I would like change the format to month_year, and see only unique value. but when I format to month_year, the duplicate happens. If I put it as date filter, user will be confused. 

Is there anyway to fix it? thank you. 

[edhans]

The date table I describe in that article allows you to easily add new columns. I have one that is Month_Year just like you say, and it will only show the unique values in a slicer, not January 2014 31 times for January. Additionally, the custom columns in Power Query have none of the problems or overhead that calculated columns have in Power BI's DAX model. See links below.

I don't know what your timeline is, but you can save your future self a lot of time by stepping back and redoing your date table in a more organized and thoughtful manner. Otherwise, you may fix this issue today, and tomorrow hit another issue. 

If it will help, here is static date table in Power Query that is Jan 1, 2020 through Dec 31, 2020. You can change those dates in the first line of the code. Put this in a blank query as noted below. It has a "Month Year" column as well as a "Month Year Sort" column so you can sort Jan, Feb, Mar instead of it doing it alphabetically by putting April and August first as it will do by default. Simply select the Month Year column in the DAX model once loaded, select Column Tools, Sort by Column, and then select Month Year Sort. 

You can do the dynamic tricks later. 😊

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Added Short Month Name" = Table.AddColumn(#"Inserted Month Name", "Short Month Name", each Text.Start([Month Name],3), type text),
    #"Added Month Year" = Table.AddColumn(#"Added Short Month Name", "Month Year", each [Short Month Name] & " " & Text.From([Year]), type text),
    #"Added Month Year Sort" = Table.AddColumn(#"Added Month Year", "Month Year Sort", each [Year]*100 + [Month], Int64.Type),
    #"Inserted Quarter Number" = Table.AddColumn(#"Added Month Year Sort", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn( #"Inserted Quarter Number","Quarter", each "Qtr " & Text.From([Quarter Number]), type text),
    #"Inserted Quarter Year Sort" = Table.AddColumn(#"Inserted Quarter", "Quarter Year Sort", each [Year] * 10 + [Quarter Number], Int64.Type),
    #"Inserted Quarter Year" = Table.AddColumn(#"Inserted Quarter Year Sort", "Quarter Year", each "Q" & Text.End([Quarter],1) & " " & Text.From([Year]), type text),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added IsFuture Boolean" = Table.AddColumn(#"Inserted Day Name", "IsFuture", each [Date] > DateTime.Date(DateTime.LocalNow()), type logical),
    #"Added IsInCurrentWeek" = Table.AddColumn(#"Added IsFuture Boolean", "IsInCurrentWeek", each Date.IsInCurrentWeek([Date]), type logical),
    #"Added IsInCurrentMonth" = Table.AddColumn(#"Added IsInCurrentWeek", "IsInCurrentMonth", each Date.IsInCurrentMonth([Date]), type logical),
    #"Added IsInCurrentQuarter" = Table.AddColumn(#"Added IsInCurrentMonth", "IsInCurrentQuarter", each Date.IsInCurrentQuarter([Date]), type logical),
    #"Added IsInCurrentYear" = Table.AddColumn(#"Added IsInCurrentQuarter","IsInCurrentYear", each Date.IsInCurrentYear([Date]), type logical)
in
    #"Added IsInCurrentYear"

[amitchandak · Accepted solution]

@Anonymous , You can create new columns for month and year in the format you want. I can see you created a few.

Month Year = FORMAT([Date],"mmm-yyyy")

Or

Month Year = FORMAT([Date],"mmmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")// Need to mark this as sort column to have correct sorting for the month year

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

If the need is there because you have some data/Table at the month level. Create a month start date

Say You have Jan-2020 format

Date = "01-" & [Month] // Change data type to date .

You can create a table at month level to but that will not work well with time intelligence