Sorting a Date Table with Broadcast Dates

Ashish_Mathur Thank you, but unfortunately, that doesn't solve my problem. Your solution sorts the broadcast days numerically, but that's not exactly what I need.

Here's what your solution did:

But the 29 is January 29, so it should be listed first. The correct order should be 29, 30, 31, 1, 2, 3, 4. That's because the Broadcast Week of January 29 contains the dates: January 29-31 and February 1-4.

Anonymous I'm not sure why you can't open my Google Drive link; it looks like another user was able to just fine.

I can't see your full code to see if it would solve my issue, but looking at your chart, you have both January and February selected. However, the Broadcast Week of January 29 should be under the Broadcast Month of February only.

Here's my date table in case it helps. Maybe there's something I'm missing.

let
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2024, 12, 31),
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
    #"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
    #"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
    #"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month", each Date.ToText([Date], "MMMM")),
    #"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Calendar Day", each Date.Day([Date])),
    #"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Day", each Date.Day([Date])),
    #"Added Custom Columns6" = Table.AddColumn(#"Added Custom Columns5", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
    #"Added Custom Columns7" = Table.AddColumn(#"Added Custom Columns6", "Broadcast Month", 
        each 
            let
                // Start of the month for the current date
                FirstOfNextMonth = Date.StartOfMonth(Date.AddMonths([Date], 1)),
                // Monday of the week containing the 1st of the next month
                FirstWeekOfNextMonth = Date.StartOfWeek(FirstOfNextMonth, Day.Monday)
            in
                if [Date] >= FirstWeekOfNextMonth 
                then Date.ToText(FirstOfNextMonth, "MMMM") 
                else Date.ToText(Date.StartOfMonth([Date]), "MMMM")
    ),
    
    #"Added Custom Columns8" = Table.AddColumn(#"Added Custom Columns7", "Broadcast Month Number", 
        each List.PositionOf({"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, [Broadcast Month]) + 1
    ),
    #"Added Custom Columns9" = Table.AddColumn(#"Added Custom Columns8", "Broadcast Quarter", 
        each 
            if [Broadcast Month] = "January" or [Broadcast Month] = "February" or [Broadcast Month] = "March" then "Qtr 1"
            else if [Broadcast Month] = "April" or [Broadcast Month] = "May" or [Broadcast Month] = "June" then "Qtr 2"
            else if [Broadcast Month] = "July" or [Broadcast Month] = "August" or [Broadcast Month] = "September" then "Qtr 3"
            else if [Broadcast Month] = "October" or [Broadcast Month] = "November" or [Broadcast Month] = "December" then "Qtr 4"
            else null
    ),
    #"Added Custom Columns10" = Table.AddColumn(#"Added Custom Columns9", "Broadcast Year", 
    each if [Date] >= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth(Date.AddMonths([Date], 1)), -Date.DayOfWeek(Date.StartOfMonth(Date.AddMonths([Date], 1)), Day.Monday)), Day.Monday) 
    then Date.Year(Date.AddMonths([Date], 1)) 
    else Date.Year([Date])
),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns10",{{"Date", type date}, {"Broadcast Month Number", Int64.Type}, {"Calendar Month Number", Int64.Type}, {"Broadcast Week", type date}})
in
    #"Changed Type"

I appreciate your help!

Sergii24 Thank you for your assistance! I'd love to pick your brain further if you can help.

By Date:

I do see that using Date will work, but the date is just too long to make the chart look nice. Is there a way to create a column with a shortened version of the date that won't cause the same problem with sorting? I know I can change the format in the Column Tools menu, but there's no option to just show the day. Even showing "mmmm d" looks a bit long, though it's doable if it's the only option.

Day of Week:

I tried creating a Day of Week column, and when I tried to sort Broadcast Day by Day of Week (1-7), I ran into the same "more than one value" error. I may have misunderstood your comment. Were you using Day of Week to sort or just use in the chart?

I appreciate your help!